OLAP Mastery Challenge — Student Handout
Goal: Demonstrate OLAP querying mastery on PostgreSQL using a snowflake data warehouse for retail sales.
Execution Environment & Setup
There are two alternatives to have access to externalised PostgreSQL SQL environments with no installation burden required:
- Aurora PostgreSQL or Aurora MySQL (version ≥8) https://sqliteonline.com
- Create an account (attention! limited number of queries allowed! so avoid testing too much).Choose the PostgreSQL option.
- Use the following script to setup your environment: https://drive.google.com/file/d/15VWiZ42u1fkUs5FVh0io-ldp96FMSeHL/view?usp=sharing
- Open in Colab the following GIST: https://gist.github.com/gevargas/664235b29f4109c1ea5045ebae15a9b2
- Use the connection information provided by your Aiven instance, which you have already created, to connect from the notebook.
Submission
Submit a .sql file containing 10 queries labelled T1..T10 or share your notebook. Ensure each query runs on a fresh database that has been loaded with the provided setup.
To Do
Write SQL Only — no explanations needed.
T1 — GROUPING SETS Multilevel Summary
Produce revenue totals for these grouping levels in a single query using GROUPING SETS:
• (year, quarter, region_name)
• (year, region_name)
• (year)
Order by year, quarter NULLS LAST, region_name NULLS LAST.
T2 — ROLLUP by Quarter and Category
Quarter/category revenue with subtotals and grand total using ROLLUP (quarter, category_name).
T3 — CUBE for Region × Channel
Compute revenue for all combinations of (region_name, channel_name), including subtotals and grand total using CUBE.
T4 — Top-3 Products per Region-Quarter
Return the top 3 products by revenue within each (region_name, year, quarter). Columns: region_name, year, quarter, product_name, revenue, rnk. Use window functions.
T5 — Quarter-over-Quarter Trend
For each region and category, compute revenue by (year, quarter) and add QoQ change and QoQ % change using LAG.
T6 — YTD (Year-to-Date) by Category
For each year and category, compute monthly revenue and a running YTD sum (window frame).
T7 — Share of Wallet (Category Share)
For each (region_name, year, quarter, category), compute revenue share = category_rev / total_rev_in_region_quarter.
T8 — Drill Across (Channel Mix)
For 2025 only, return revenue by (quarter, channel_name) and compute the % that each channel contributes in that quarter.
T9 — Detect Declines
List (region_name, category_name) that experienced a QoQ revenue decline at least once in the series.
T10 — Dense Rollup + Filter
Roll up by (year, category_name) with grand total; filter to show only rows where revenue > 200. Order by year, category, with totals last.