aggregate_facts_with_agg_dim.sql

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
  FROM sales_facts AS facts
    JOIN
       product_aggregation_dim AS products
    ON facts.product_id = products.descendant_node_natural_key
GROUP BY products.ancestor_node_name
       , products.ancestor_level_name
       , products.ancestor_level_number
       , products.ancestor_node_sort_order
ORDER BY products.ancestor_node_sort_order ASC
;