1

DB-Fiddle

CREATE TABLE sales (
    id int auto_increment primary key,
    country VARCHAR(255),
    sales_date DATE,
    sales_volume INT,
    fix_costs INT
);

INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 

("DE", "2020-01-03", "500", "0"),
("NL", "2020-01-03", "320", "0"),
("FR", "2020-01-03", "350", "0"),
("None", "2020-01-31", "0", "2000"),

("DE", "2020-02-15", "0", "0"),
("NL", "2020-02-15", "0", "0"),
("FR", "2020-02-15", "0", "0"),
("None", "2020-02-29", "0", "5000"),

("DE", "2020-03-27", "180", "0"),
("NL", "2020-03-27", "670", "0"),
("FR", "2020-03-27", "970", "0"),
("None", "2020-03-31", "0", "4000");

Expected Result:

sales_date   |   country    |   sales_volume   |     fix_costs
-------------|--------------|------------------|------------------------------------------
2020-01-03   |     DE       |       500        |     37.95  (= 2000/31 = 64.5 x 0.59)
2020-01-03   |     FR       |       350        |     26.57  (= 2000/31 = 64.5 x 0.41)
2020-01-03   |     NL       |       320        |      0.00
-------------|--------------|------------------|------------------------------------------
2020-02-15   |     DE       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     FR       |         0        |     86.21  (= 5000/28 = 172.4 x 0.50)  
2020-02-15   |     NL       |         0        |      0.00
-------------|--------------|------------------|------------------------------------------    
2020-03-27   |     DE       |       180        |     20.20  (= 4000/31 = 129.0 x 0.16) 
2020-03-27   |     FR       |       970        |    108.84  (= 4000/31 = 129.0 x 0.84)   
2020-03-27   |     NL       |       670        |      0.00
-------------|--------------|------------------|-------------------------------------------

The column fix_costs in the expected result is calculated as the following:

Step 1) Get the daily rate of the fix_costs per month. (2000/31 = 64.5; 5000/29 = 172.4; 4000/31 = 129.0)
Step 2) Split the daily value to the countries DE and FR based on their share in the sales_volume.
Step 3) In case the sales_volume is 0 the daily rate gets split 50/50 to DE and FR as you can see for 2020-12-02.

In order to achieve this I tried to go with this query but could not make it work:

SELECT 
    sales_date, 
    country, 
    SUM(sales_volume),
       (CASE WHEN country = 'NL' THEN 0
             WHEN SUM(CASE WHEN country <> 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date) > 0
             THEN ((f.fix_costs/ DAY(LAST_DAY(sales_date))) *
                   sales_volume / NULLIF(SUM(CASE WHEN country <> 'NL' THEN sales_volume END) OVER (PARTITION BY sales_date), 0)
                  )
             ELSE (f.fix_costs / DAY(LAST_DAY(sales_date))) * 1 / SUM(country <> 'NL') OVER (PARTITION by sales_date)
        END) AS imputed_fix_costs
FROM sales s

    CROSS JOIN
     (SELECT
      SUM(fix_costs) as fix_costs
      FROM sales
      WHERE country = 'None'
     ) f

WHERE country <> "None"
GROUP BY 1,2;

What do I need to modify in the query to achieve the expected result?

Michi
  • 4,663
  • 6
  • 33
  • 83
  • Do you ever look at your expected results given your input data - in this case your dates in results don't match the input dates. – P.Salmon Jan 14 '21 at 14:46

1 Answers1

0

You need to fix the FROM clause, so you are only including the fixed costs for each month:

FROM sales s CROSS JOIN
     (SELECT LAST_DAY(sales_date) as month_ld, SUM(fix_costs) as fix_costs
      FROM sales
      WHERE country = 'None'
      GROUP BY month_ld
     ) f
     ON f.month_ld = LAST_DAY(s.sales_date)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786