CREATE TABLE sales (
id SERIAL PRIMARY KEY,
event_date DATE,
country VARCHAR,
channel VARCHAR,
sales DECIMAL
);
INSERT INTO sales
(event_date, country, channel, sales)
VALUES
('2020-02-08', 'DE', 'channel_01', '500'),
('2020-02-08', 'DE', 'channel_02', '400'),
('2020-02-08', 'DE', 'channel_03', '200'),
('2020-02-08', 'FR', 'channel_01', '900'),
('2020-02-08', 'FR', 'channel_02', '800'),
('2020-02-08', 'NL', 'channel_01', '100'),
('2020-03-20', 'DE', 'channel_01', '0'),
('2020-03-20', 'FR', 'channel_01', '0'),
('2020-03-20', 'FR', 'channel_02', '0'),
('2020-03-20', 'FR', 'channel_03', '0'),
('2020-03-20', 'NL', 'channel_01', '0'),
('2020-04-15', 'DE', 'channel_01', '700'),
('2020-04-15', 'FR', 'channel_01', '500'),
('2020-04-15', 'NL', 'channel_01', '850'),
('2020-04-15', 'NL', 'channel_02', '250'),
('2020-04-15', 'NL', 'channel_03', '300');
Expected Result:
event_date | country | share_per_day_per_country | details of share calculation
------------|-----------|----------------------------|--------------------------------------------
2020-02-08 | DE | 0.379 | = (500+400+200) / (500+400+200+900+800+100)
2020-02-08 | FR | 0.586 | = (900+800) / (500+400+200+900+800+100)
2020-02-08 | NL | 0.034 | = (100) / (500+400+200+900+800+100)
------------|-----------|----------------------------|--------------------------------------------
2020-03-20 | DE | 0.333 | = equal split in case of 0 sales
2020-03-20 | FR | 0.333 | = equal split in case of 0 sales
2020-03-20 | NL | 0.333 | = equal split in case of 0 sales
------------|-----------|----------------------------|--------------------------------------------
2020-04-15 | DE | 0.269 | = (700) / (700+500+850+250+300)
2020-04-15 | FR | 0.192 | = (500) / (700+500+850+250+300)
2020-04-15 | NL | 0.538 | = (850+250+300) / (700+500+850+250+300)
In the expected result I want to
- calculate the share of the sales per country per day
- in case there is a day with no sales the share should be divided equaly to the number of countries.
In order to achieve this so far I have developed this query:
SELECT
t1.event_date,
t1.country,
t1.sales,
t1.total_sales_per_country,
t1.total_sales_per_day,
(CASE WHEN SUM(t1.sales) OVER (PARTITION BY t1.event_date) = 0 THEN
100/(COUNT(t1.country) OVER (PARTITION BY t1.event_date))/100::decimal
ELSE t1.total_sales_per_country / t1.total_sales_per_day END) AS share_per_day_per_country
FROM
(SELECT
s.event_date,
s.country,
s.sales,
SUM(s.sales) OVER (PARTITION BY s.event_date) AS total_sales_per_day,
SUM(s.sales) OVER (PARTITION BY s.event_date, s.country) AS total_sales_per_country
FROM sales s
GROUP BY 1,2,3
ORDER BY 1,2) t1
GROUP BY 1,2,3,4,5
ORDER BY 1,2
This query almost gives me the correct result.
However, instead of listing each event_date only one time it lists them multiple times.
I have tried a few ways (e.g. DSTINCT pl.event_date) to fix this issue but none of them worked.
How do I have to modify the query to get the entire expected result?