jesse042811/sql_case_study

Tables given with ID, Datetime, metricName, and metricValue, but want to repivot in a manner where we can focus on specific metricName and tracks which versions they used based on the metricValue column.

SQL_case_study

Tables given with ID, Datetime, metricName, and metricValue, but want to repivot in a manner where we can focus on specific metricName and tracks which versions they used based on the metricValue column.

I've posted by solution in finalSQL.sql. Feel free to leave a comment on how I can improve the solution

Requirements/Information

Convert a table of metrics into a Version history table

• We need to convert the contents of the data.csv file into the contents of the finalData.csv file
• Version1 is a major software update
• Version2 is a minor software update

Desired Final Format

clientID	dateTime	Version1	Version2
A	2021-01-01	1.0	A1
A	2021-01-02	1.0	A2
B	2022-01-01	3.0	A4

Must use SQL to keep as much processing in one system as possible

• This would be easier in Python or R but then we’d have to extract the data from the database, transform it, then reimport it

For each deviceID, there’s only a record when there’s an update to the system

If we just pivot our records then we get a lot of nulls

Resources

The initial data is in data.csv

You can insert the data into https://sqliteonline.com/ for a quick and easy SQL Database

What You'll Learn / Use

My solution uses:

• The PIVOT operator
• WITH to create a Continuous Table Expression (CTE)
• OVER to use window functions

Credit for solution

https://www.andrewvillazon.com/forward-fill-values-t-sql/#:~:text=To achieve the forward-filling,current row value is NULL .&text=Now we can SELECT the,rows come out as expected.&text=At this point%2C we've forward-filled