{"id":99,"date":"2025-11-04T18:45:14","date_gmt":"2025-11-04T18:45:14","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=99"},"modified":"2025-11-04T18:45:14","modified_gmt":"2025-11-04T18:45:14","slug":"mastering-olap","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/mastering-olap\/","title":{"rendered":"Mastering OLAP"},"content":{"rendered":"\n<h4 class=\"wp-block-heading has-medium-font-size\"><strong>OLAP Mastery Challenge \u2014 Student Handout<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Goal<\/strong>: Demonstrate OLAP querying mastery on PostgreSQL using a snowflake data warehouse for retail sales.<\/p>\n\n\n\n<h4 class=\"wp-block-heading has-medium-font-size\"><strong>Execution Environment &amp; Setup<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">There are two alternatives to have access to externalised PostgreSQL SQL environments with no installation burden required:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Aurora PostgreSQL or Aurora MySQL (version \u22658)\u00a0<a href=\"https:\/\/sqliteonline.com\/\">https:\/\/sqliteonline.com<\/a>\n<ul class=\"wp-block-list\">\n<li><ol><li>Create an account (attention!\u00a0<strong>limited number<\/strong>\u00a0of queries allowed! so avoid testing too much).Choose the PostgreSQL option.<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li>Use the following script to setup your environment:\u00a0<a href=\"https:\/\/drive.google.com\/file\/d\/15VWiZ42u1fkUs5FVh0io-ldp96FMSeHL\/view?usp=sharing\">https:\/\/drive.google.com\/file\/d\/15VWiZ42u1fkUs5FVh0io-ldp96FMSeHL\/view?usp=sharing<\/a><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Open in Colab the following GIST:\u00a0<a href=\"https:\/\/gist.github.com\/gevargas\/664235b29f4109c1ea5045ebae15a9b2\">https:\/\/gist.github.com\/gevargas\/664235b29f4109c1ea5045ebae15a9b2<\/a><\/li>\n\n\n\n<li>Use the connection information provided by your Aiven instance, which you have already created, to connect from the notebook.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong><em>Submission<\/em><\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Submit a&nbsp;.sql file&nbsp;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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">To Do&nbsp;<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Write SQL Only \u2014 no explanations needed.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T1 \u2014 GROUPING SETS Multilevel Summary<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Produce revenue totals for these grouping levels in a single query using&nbsp;GROUPING SETS:<br>&nbsp;&nbsp;\u2022 (year, quarter, region_name)<br>&nbsp;&nbsp;\u2022 (year, region_name)<br>&nbsp;&nbsp;\u2022 (year)<br>Order by year, quarter&nbsp;NULLS LAST, region_name&nbsp;NULLS LAST.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T2 \u2014 ROLLUP by Quarter and Category<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Quarter\/category revenue with subtotals and grand total using&nbsp;ROLLUP&nbsp;(quarter, category_name).<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T3 \u2014 CUBE for Region \u00d7 Channel<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Compute revenue for all combinations of (region_name, channel_name), including subtotals and grand total using&nbsp;CUBE.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T4 \u2014 Top-3 Products per Region-Quarter<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Return the top 3 products by revenue within each (region_name, year, quarter). Columns:&nbsp;region_name, year, quarter, product_name, revenue, rnk. Use window functions.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T5 \u2014 Quarter-over-Quarter Trend<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For each region and category, compute revenue by (year, quarter) and add&nbsp;QoQ&nbsp;change and&nbsp;QoQ %&nbsp;change using&nbsp;LAG.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T6 \u2014 YTD (Year-to-Date) by Category<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For each year and category, compute monthly revenue and a running&nbsp;YTD sum&nbsp;(window frame).<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T7 \u2014 Share of Wallet (Category Share)<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For each (region_name, year, quarter, category), compute revenue&nbsp;share = category_rev \/ total_rev_in_region_quarter.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T8 \u2014 Drill Across (Channel Mix)<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For 2025 only, return revenue by (quarter, channel_name) and compute the&nbsp;%&nbsp;that each channel contributes in that quarter.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T9 \u2014 Detect Declines<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">List (region_name, category_name) that experienced a&nbsp;QoQ&nbsp;revenue decline at least once in the series.<\/p>\n\n\n\n<h3 class=\"wp-block-heading has-small-font-size\"><em>T10 \u2014 Dense Rollup + Filter<\/em><\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Roll up by (year, category_name) with grand total; filter to show only rows where&nbsp;revenue &gt; 200. Order by&nbsp;year, category, with totals last.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>OLAP Mastery Challenge \u2014 Student Handout Goal: Demonstrate OLAP querying mastery on PostgreSQL using a snowflake data warehouse for retail sales. Execution Environment &amp; Setup There are two alternatives to have access to externalised PostgreSQL SQL environments with no installation &hellip; <a href=\"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/mastering-olap\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-99","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/99","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/comments?post=99"}],"version-history":[{"count":1,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/99\/revisions"}],"predecessor-version":[{"id":101,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/99\/revisions\/101"}],"wp:attachment":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/media?parent=99"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}