Philip Moore prmoore77
prmoore77
/ sales_facts_table_contents.csv
Last active
June 7, 2022 18:43
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| product_name | product_id | customer_id | date_id | unit_quantity | sales_amount | |
|---|---|---|---|---|---|---|
| Hershey Bar | 201 | Phil | 2022-01-01 | 1.000 | 3.000 | |
| Hershey Bar | 201 | Lottie | 2022-01-02 | 5.000 | 15.000 | |
| Nerds | 202 | Kalie | 2022-01-02 | 2.000 | 5.000 | |
| Tomatoes | 102 | Phil | 2022-01-02 | 2.000 | 2.000 | |
| Spinach | 101 | Popeye | 2022-01-03 | 10.000 | 5.000 | |
| Spinach | 101 | Brutus | 2022-01-04 | 1.000 | 0.500 | |
| Spinach | 101 | Lottie | 2022-01-04 | 1.000 | 0.500 | |
| Spinach | 101 | Phil | 2022-01-05 | 2.000 | 2.000 |
prmoore77
/ aggregate_facts_with_agg_dim.sql
Last active
June 7, 2022 18:50
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT -- Use LPAD here to indent the node information based upon its depth in the hierarchy | |
| LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_level_name AS product_level_name | |
| , LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_node_name AS product_node_name | |
| -- Aggregates | |
| , SUM (facts.sales_amount) AS sum_sales_amount | |
| , SUM (facts.unit_quantity) AS sum_unit_quantity | |
| , COUNT (DISTINCT facts.customer_id) AS distinct_customer_count | |
| , COUNT (*) AS count_of_fact_records |
prmoore77
/ aggregation_results.csv
Last active
June 7, 2022 18:51
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| product_level_name | product_node_name | sum_sales_amount | sum_unit_quantity | distinct_customer_count | count_of_fact_records | |
|---|---|---|---|---|---|---|
| Total Products | All Products | 33.000 | 24.000 | 5 | 8 | |
| -------Category | -------Candy | 23.000 | 8.000 | 3 | 3 | |
| --------------UPC | --------------Hershey Bar | 18.000 | 6.000 | 2 | 2 | |
| --------------UPC | --------------Nerds | 5.000 | 2.000 | 1 | 1 | |
| -------Category | -------Produce | 10.000 | 16.000 | 4 | 5 | |
| --------------UPC | --------------Spinach | 8.000 | 14.000 | 4 | 4 | |
| --------------UPC | --------------Tomatoes | 2.000 | 2.000 | 1 | 1 |
prmoore77
/ aggregations_with_rollup.sql
Last active
June 7, 2022 20:05
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH rollup_aggregations AS ( | |
| SELECT CASE WHEN GROUPING (level_3_node_id) = 0 | |
| THEN products.level_3_node_id | |
| WHEN GROUPING (level_2_node_id) = 0 | |
| THEN products.level_2_node_id | |
| WHEN GROUPING (level_1_node_id) = 0 | |
| THEN products.level_1_node_id | |
| END AS product_node_id | |
| -- Aggregates | |
| , SUM (facts.sales_amount) AS sum_sales_amount |
prmoore77
/ rollup_aggregation_results.csv
Created
June 7, 2022 19:03
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| product_level_name | product_node_name | sum_sales_amount | sum_unit_quantity | distinct_customer_count | count_of_fact_records | |
|---|---|---|---|---|---|---|
| Total Products | All Products | 33.000 | 24.000 | 5 | 8 | |
| -------Category | -------Candy | 23.000 | 8.000 | 3 | 3 | |
| --------------UPC | --------------Hershey Bar | 18.000 | 6.000 | 2 | 2 | |
| --------------UPC | --------------Nerds | 5.000 | 2.000 | 1 | 1 | |
| -------Category | -------Produce | 10.000 | 16.000 | 3 | 5 | |
| --------------UPC | --------------Spinach | 8.000 | 14.000 | 2 | 4 | |
| --------------UPC | --------------Tomatoes | 2.000 | 2.000 | 1 | 1 |
prmoore77
/ multiple_dimension_aggregation.sql
Last active
June 7, 2022 20:27
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| -- Product Dimension | |
| LPAD (' ', (products.ancestor_level_number - 1) * 7, ' ') | |
| || products.ancestor_level_name AS product_level_name | |
| , LPAD (' ', (products.ancestor_level_number - 1) * 7, ' ') | |
| || products.ancestor_node_name AS product_node_name | |
| -- Geography Dimension | |
| , LPAD (' ', (geographies.ancestor_level_number - 1) * 7, ' ') | |
| || geographies.ancestor_level_name AS geography_level_name | |
| , LPAD (' ', (geographies.ancestor_level_number - 1) * 7, ' ') |
prmoore77
/ exploded_aggregation_technique_break_down.sql
Last active
June 8, 2022 14:23
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT | |
| -- Product Ancestor Attributes | |
| LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_level_name AS product_ancestor_level_name | |
| , LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_node_name AS product_ancestor_node_name | |
| -- Product Descendant Attributes | |
| , LPAD ('-', (products.descendant_level_number - 1) * 7, '-') | |
| || products.descendant_level_name AS product_descendant_level_name | |
| , LPAD ('-', (products.descendant_level_number - 1) * 7, '-') |
prmoore77
/ exploded_aggregation_breakdown_results.csv
Created
June 8, 2022 14:23
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| product_ancestor_level_name | product_ancestor_node_name | product_descendant_level_name | product_descendant_node_name | customer_id | sales_amount | |
|---|---|---|---|---|---|---|
| Total Products | All Products | --------------UPC | --------------Hershey Bar | Lottie | 15.000 | |
| Total Products | All Products | --------------UPC | --------------Hershey Bar | Phil | 3.000 | |
| Total Products | All Products | --------------UPC | --------------Nerds | Kalie | 5.000 | |
| -------Category | -------Candy | --------------UPC | --------------Hershey Bar | Lottie | 15.000 | |
| -------Category | -------Candy | --------------UPC | --------------Hershey Bar | Phil | 3.000 | |
| -------Category | -------Candy | --------------UPC | --------------Nerds | Kalie | 5.000 | |
| --------------UPC | --------------Hershey Bar | --------------UPC | --------------Hershey Bar | Lottie | 15.000 | |
| --------------UPC | --------------Hershey Bar | --------------UPC | --------------Hershey Bar | Phil | 3.000 | |
| --------------UPC | --------------Nerds | --------------UPC | --------------Nerds | Kalie | 5.000 |
prmoore77
/ targeted_dimension_aggregation.sql
Created
June 8, 2022 14:32
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT -- Use LPAD here to indent the node information based upon its depth in the hierarchy | |
| LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_level_name AS product_level_name | |
| , LPAD ('-', (products.ancestor_level_number - 1) * 7, '-') | |
| || products.ancestor_node_name AS product_node_name | |
| -- Aggregates | |
| , SUM (facts.sales_amount) AS sum_sales_amount | |
| , SUM (facts.unit_quantity) AS sum_unit_quantity | |
| , COUNT (DISTINCT facts.customer_id) AS distinct_customer_count | |
| , COUNT (*) AS count_of_fact_records |
prmoore77
/ targeted_aggregation_results.csv
Created
June 8, 2022 14:34
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| product_level_name | product_node_name | sum_sales_amount | sum_unit_quantity | distinct_customer_count | count_of_fact_records | |
|---|---|---|---|---|---|---|
| Total Products | All Products | 23.000 | 8.000 | 3 | 3 | |
| -------Category | -------Candy | 23.000 | 8.000 | 3 | 3 | |
| --------------UPC | --------------Hershey Bar | 18.000 | 6.000 | 2 | 2 | |
| --------------UPC | --------------Nerds | 5.000 | 2.000 | 1 | 1 |