learning-it by example

#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.

1. SQLite has no PIVOT keyword. How does the video pivot long sales rows into one column per quarter?
2. In the SQLite pivot of made-up sales, West had no sales in quarter 1. What did SUM show for that cell, and what did TOTAL show?
3. Why is East missing from the pivoted result, which shows only North, South and West?
4. North sold 10 and 5 in quarter 1. After a pivot (North, Q1 = 15) and an unpivot back to rows, what does North's quarter 1 look like?
5. SQLite has no LATERAL. What did the video use to get the best quarter and the top two sales per region?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos