Calculate share of value per date and country and handle zero values separately

Viewed 29

DB-Fiddle

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

  1. calculate the share of the sales per country per day
  2. 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?

1 Answers

Remove t1.sales and t1.total_sales_per_country from the select list. Now use distinct instead of group by.

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');

Query:

SELECT
distinct t1.event_date,
t1.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
  ORDER BY 1,2

Output:

event_date country total_sales_per_day share_per_day_per_country
2020-02-08 DE 2900 0.37931034482758620690
2020-02-08 FR 2900 0.58620689655172413793
2020-02-08 NL 2900 0.03448275862068965517
2020-03-20 DE 0 0.33000000000000000000
2020-03-20 FR 0 0.33000000000000000000
2020-03-20 NL 0 0.33000000000000000000
2020-04-15 DE 2600 0.26923076923076923077
2020-04-15 FR 2600 0.19230769230769230769
2020-04-15 NL 2600 0.53846153846153846154

db<>fiddle here

Related