#125 SQL and analytics 6.7 min
Pivot, Unpivot and Lateral-Style Queries in SQLite
Six made-up sales pivot into 3 rows in SQLite, which has no PIVOT or LATERAL keyword.
Run the six queries
Copy the code folder first. Run python run.py pivot total, python run.py unpivot values and python run.py best top. Find the row where West has NULL in the quarter 1 column in the first command. In the second command, find the one row that appears in unpivot but is dropped by values.
A hint
The values version ends with a filter that drops null amounts.
Else zero hides a gap
In queries.py, the PIVOT query has CASE WHEN quarter = 'Q1' THEN amount END. Change the Q1 part so it reads THEN amount ELSE 0 END, then run python run.py pivot. On this machine West's quarter 1 now shows 0 instead of NULL. Write one sentence on why that makes it harder to tell no sales from sales of zero.
A hint
The video lists this as a mistake to avoid.
Give East a sale
In sales.py, add a new sale to the SALES list: (7, "East", "Q1", 20). Predict how East looks in python run.py pivot total best top before you run it. On this machine East now appears in the pivot with 20 in quarter 1 and NULL in quarter 2, and the best query shows East, Q1, 20.
A hint
East appears in the pivot only if it has at least one sale row.
Top one instead of top two
In queries.py, the TOP_TWO query ends its inner subquery with LIMIT 2. Change it to LIMIT 1 and predict how many rows python run.py top prints for North. On this machine North has one row, quarter Q1 with 10, and the other regions each keep one row too.
A hint
The left join keeps every region, and the subquery decides which sale ids are allowed.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
Six made-up sales in SQLite only; DuckDB, SQL Server and PostgreSQL features are described from their docs, not run, and no timings are measured.
