You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
24 lines
1.1 KiB
24 lines
1.1 KiB
2 years ago
|
-- Query 1: "2 last columns with the sum() analytic function" (?)
|
||
|
|
-- From the screenshots, this query should be a copy of the query 1 of the group-by part,
|
||
|
|
-- with two new columns that add a running sum, one global and one partitionned by year.
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
calendar_year_lookup.year,
|
||
|
|
outlet_lookup.shop_name,
|
||
|
|
SUM(shop_facts.amount_sold) AS total_amount_sold,
|
||
|
|
SUM(shop_facts.quantity_sold) AS total_quantity_sold,
|
||
|
|
SUM(SUM(shop_facts.amount_sold))
|
||
|
|
OVER (ORDER BY calendar_year_lookup.year, outlet_lookup.shop_name)
|
||
|
|
AS total_amount_sold_global_running_sum,
|
||
|
|
SUM(SUM(shop_facts.amount_sold))
|
||
|
|
OVER (
|
||
|
|
PARTITION BY calendar_year_lookup.year
|
||
|
|
ORDER BY calendar_year_lookup.year, outlet_lookup.shop_name
|
||
|
|
)
|
||
|
|
AS total_amount_sold_yearly_running_sum
|
||
|
|
FROM outlet_lookup
|
||
|
|
INNER JOIN shop_facts ON shop_facts.shop_code = outlet_lookup.shop_code
|
||
|
|
INNER JOIN calendar_year_lookup ON shop_facts.week_key = calendar_year_lookup.week_key
|
||
|
|
GROUP BY outlet_lookup.shop_code, calendar_year_lookup.year
|
||
|
|
ORDER BY calendar_year_lookup.year, outlet_lookup.shop_name;
|