In the second part, students instantiate the same cube virtually<\/em> by creating and populating a snowflake data warehouse schema<\/strong> in PostgreSQL. They then run advanced SQL queries ( Learning Objective<\/em>: Practice advanced SQL aggregation and analytics techniques (GROUPING SETS, ROLLUP, CUBE, and window functions) on a dimensional model.<\/p>\n\n\n\n Figure 1. OLAP Cube representing Product \u00d7 Region \u00d7 Time dimensions and aggregation operations (slice, dice, drill-down, roll-up).<\/em><\/p>\n\n\n\n There are two alternatives to have access to externalized PostgreSQL SQL environments with no installation burden required:<\/p>\n\n\n\n <\/p>\n\n\n\n 2. PostgreSQL externalized cloud (recommended<\/strong>)<\/p>\n\n\n\n Analyse and test the following query expressions<\/em><\/strong><\/p>\n\n\n\n This query calculates total sales revenue at different aggregation levels: by Year + Quarter + Region, by Year + Region, and by Year only. GROUPING SETS lets you produce all these summaries in one query instead of running several GROUP BYstatements.<\/p>\n\n\n\n SELECT This query computes total revenue for each quarter and category, including subtotals per quarter and a grand total. The ROLLUP operator automatically adds ‘All categories’ and ‘All quarters’ rows.<\/p>\n\n\n\n SELECT This builds a cross-tab combining regions and sales channels. The CUBE operator creates totals per region, totals per channel, and a global total.<\/p>\n\n\n\n SELECT Using window functions, this query ranks products by total revenue within each region and quarter. DENSE_RANK() assigns ranks, and we filter to keep only the top three per group.<\/p>\n\n\n\n WITH revenue_p AS ( This measures how revenue changes from one quarter to the next for each region and category. LAG() retrieves the previous quarter\u2019s value to compute absolute and percentage change.<\/p>\n\n\n\n WITH base AS ( This tracks cumulative revenue for each category within a year. SUM() OVER (…) adds up sales month by month to show how revenue accumulates.<\/p>\n\n\n\n WITH monthly AS ( This calculates how much each category contributes to total sales within each region and quarter, showing each category\u2019s share as a percentage of regional revenue.<\/p>\n\n\n\n WITH base AS ( For the year 2025, this query measures what percentage of sales came from each channel (Online, Store) within every quarter.<\/p>\n\n\n\n WITH q AS ( This identifies all region\u2013category pairs that experienced at least one drop in revenue between consecutive quarters.<\/p>\n\n\n\n WITH series AS ( This variation of ROLLUP filters results to show only groups with total revenue greater than 200. COALESCE replaces NULLtotals with zero for safe comparison.<\/p>\n\n\n\n SELECT dt.year, dc.category_name, SUM(fs.revenue) AS revenue Student Handout \u2014 OLAP Ludic Exercise + Snowflake DW Context This hands-on exercise bridges the tangible and the analytical dimensions of OLAP learning. Students begin by building and manipulating a physical cube using moulding paste to represent three analytical dimensions \u2014 Product, Region, and Time \u2014 … Continue reading GROUPING SETS<\/code>, ROLLUP<\/code>, CUBE<\/code>, and window functions) to compute summaries, growth indicators, and category shares.<\/p>\n\n\n\nMaterial<\/h3>\n\n\n\n
\n
Part I: Build an OLAP Cube with moulding paste (Product \u00d7 Region \u00d7 Time). Then map each physical operation to an SQL statement<\/h3>\n\n\n\n
\n
![\"\"](\"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-content\/uploads\/sites\/64\/2025\/11\/image-1024x1024.png\")
<\/figure>\n\n\n\nPart II \u2014 Snowflake DW: Build, Populate, Query<\/strong><\/h3>\n\n\n\n
\n
OLAP Queries to Try<\/h3>\n\n\n\n
Setting up a querying environment<\/em><\/strong><\/h4>\n\n\n\n
\n
\n
\n
Q1 \u2013 Multilevel Summary with GROUPING SETS<\/h4>\n\n\n\n
dt.year,
dt.quarter,
dr.region_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_region dr ON fs.region_id = dr.region_id
GROUP BY GROUPING SETS (
(dt.year, dt.quarter, dr.region_name),
(dt.year, dr.region_name),
(dt.year)
)
ORDER BY dt.year, dt.quarter NULLS LAST, dr.region_name NULLS LAST;<\/p>\n\n\n\nQ2 \u2013 Hierarchical Rollup by Quarter and Category<\/h4>\n\n\n\n
dt.quarter,
dc.category_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY ROLLUP (dt.quarter, dc.category_name)
ORDER BY dt.quarter NULLS LAST, dc.category_name NULLS LAST;<\/p>\n\n\n\nQ3 \u2013 Two-Dimensional Cube of Region \u00d7 Channel<\/h4>\n\n\n\n
dr.region_name,
ch.channel_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_region dr ON fs.region_id = dr.region_id
JOIN dim_channel ch ON fs.channel_id = ch.channel_id
GROUP BY CUBE (dr.region_name, ch.channel_name)
ORDER BY dr.region_name NULLS LAST, ch.channel_name NULLS LAST;<\/p>\n\n\n\nQ4 \u2013 Top-3 Products per Region and Quarter<\/h4>\n\n\n\n
SELECT dr.region_name, dt.year, dt.quarter, dp.name AS product_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_region dr ON fs.region_id = dr.region_id
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
GROUP BY dr.region_name, dt.year, dt.quarter, dp.name
),
ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY region_name, year, quarter ORDER BY revenue DESC) AS rnk
FROM revenue_p
)
SELECT region_name, year, quarter, product_name, revenue, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY region_name, year, quarter, rnk;<\/p>\n\n\n\nQ5 \u2013 Quarter-over-Quarter (QoQ) Growth<\/h4>\n\n\n\n
SELECT dr.region_name, dc.category_name, dt.year, dt.quarter,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_region dr ON fs.region_id = dr.region_id
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY dr.region_name, dc.category_name, dt.year, dt.quarter
)
SELECT region_name, category_name, year, quarter, revenue,
revenue – LAG(revenue) OVER (PARTITION BY region_name, category_name ORDER BY year, quarter) AS qoq_change,
CASE WHEN LAG(revenue) OVER (PARTITION BY region_name, category_name ORDER BY year, quarter) = 0
THEN NULL
ELSE ROUND(100.0 * (revenue – LAG(revenue) OVER (PARTITION BY region_name, category_name ORDER BY year, quarter)) \/
NULLIF(LAG(revenue) OVER (PARTITION BY region_name, category_name ORDER BY year, quarter),0), 2)
END AS qoq_pct
FROM base
ORDER BY region_name, category_name, year, quarter;<\/p>\n\n\n\nQ6 \u2013 Year-to-Date (YTD) Running Total<\/h4>\n\n\n\n
SELECT dt.year, dt.month, dc.category_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY dt.year, dt.month, dc.category_name
)
SELECT year, month, category_name, revenue,
SUM(revenue) OVER (PARTITION BY year, category_name ORDER BY month
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_revenue
FROM monthly
ORDER BY year, month, category_name;<\/p>\n\n\n\nQ7 \u2013 Category Market Share<\/h4>\n\n\n\n
SELECT dr.region_name, dt.year, dt.quarter, dc.category_name,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_region dr ON fs.region_id = dr.region_id
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY dr.region_name, dt.year, dt.quarter, dc.category_name
),
tot AS (
SELECT region_name, year, quarter, SUM(revenue) AS total_rev
FROM base
GROUP BY region_name, year, quarter
)
SELECT b.region_name, b.year, b.quarter, b.category_name, b.revenue,
ROUND(100.0 * b.revenue \/ t.total_rev, 2) AS pct_share
FROM base b
JOIN tot t USING (region_name, year, quarter)
ORDER BY b.region_name, b.year, b.quarter, b.category_name;<\/p>\n\n\n\nQ8 \u2013 Channel Mix per Quarter<\/h4>\n\n\n\n
SELECT dt.quarter, ch.channel_name, SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_channel ch ON fs.channel_id = ch.channel_id
WHERE dt.year = 2025
GROUP BY dt.quarter, ch.channel_name
),
tot AS (
SELECT quarter, SUM(revenue) AS total_rev FROM q GROUP BY quarter
)
SELECT q.quarter, q.channel_name, q.revenue,
ROUND(100.0 * q.revenue \/ t.total_rev, 2) AS pct_in_quarter
FROM q JOIN tot t USING (quarter)
ORDER BY q.quarter, q.channel_name;<\/p>\n\n\n\nQ9 \u2013 Detect Revenue Declines<\/h4>\n\n\n\n
SELECT dr.region_name, dc.category_name, dt.year, dt.quarter,
SUM(fs.revenue) AS revenue
FROM fact_sales fs
JOIN dim_region dr ON fs.region_id = dr.region_id
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY dr.region_name, dc.category_name, dt.year, dt.quarter
),
lagged AS (
SELECT *, LAG(revenue) OVER (PARTITION BY region_name, category_name ORDER BY year, quarter) AS prev_rev
FROM series
)
SELECT DISTINCT region_name, category_name
FROM lagged
WHERE prev_rev IS NOT NULL AND revenue < prev_rev
ORDER BY region_name, category_name;<\/p>\n\n\n\nQ10 \u2013 Filtered Rollup<\/h4>\n\n\n\n
FROM fact_sales fs
JOIN dim_time dt ON fs.time_id = dt.time_id
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_subcategory ds ON dp.subcategory_id = ds.subcategory_id
JOIN dim_category dc ON ds.category_id = dc.category_id
GROUP BY ROLLUP (dt.year, dc.category_name)
HAVING COALESCE(SUM(fs.revenue),0) > 200
ORDER BY dt.year NULLS LAST, dc.category_name NULLS LAST;<\/p>\n","protected":false},"excerpt":{"rendered":"