Split fix monthly value to days and countries based on revenue shares

user_4934150 0
Viewed 34

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-12-01", "500", "0"),
("NL", "2020-12-01", "320", "0"),
("FR", "2020-12-01", "350", "0"),

("DE", "2020-12-02", "700", "0"),
("NL", "2020-12-02", "420", "0"),
("FR", "2020-12-02", "180", "0"),

("DE", "2020-12-03", "0", "0"),
("NL", "2020-12-03", "0", "0"),
("FR", "2020-12-03", "0", "0"),

("None", "2020-12-31", "0", "2000");

Expected Result:

sales_date   |   country    |   sales_volume   |     fix_costs
-------------|--------------|------------------|------------------------------------------
2020-12-01   |     DE       |       500        |     37.95  (=2000/31 = 64.5 * 0.59)
2020-12-01   |     FR       |       350        |     26.57  (=2000/31 = 64.5 * 0.41)
2020-12-01   |     NL       |       320        |      0.00
-------------|--------------|------------------|------------------------------------------
2020-12-02   |     DE       |       700        |     51.32  (=2000/31 = 64.5 * 0.8)   
2020-12-02   |     FR       |       180        |     13.20  (=2000/31 = 64.5 * 0.2)  
2020-12-02   |     NL       |       420        |      0.00
-------------|--------------|------------------|------------------------------------------    
2020-12-03   |     DE       |         0        |     32.26  (=2000/31 = 64.5 * 0.5) 
2020-12-03   |     FR       |         0        |     32.26  (=2000/31 = 64.5 * 0.5)    
2020-12-03   |     NL       |         0        |      0.00
-------------|--------------|------------------|-------------------------------------------

In the result above I want to split a fix_cost value per month (in this case 2000) to the country DE and FR per day based on the daily sales_volume in each of the selected countries. So far I have used the solution from this question which worked perfectly:

SELECT
    sales_date, 
    country, 
    SUM(sales_volume),
    (CASE WHEN country <> 'NL'
        THEN SUM(SUM(fix_costs)) over(partition BY year(sales_date), month(sales_date))
            / day(last_day(sales_date)) 
            * SUM(sales_volume)
            / SUM(CASE WHEN country <> 'NL' THEN SUM(sales_volume) ELSE 0 END) over(partition BY sales_date)
    END) AS fix_cost_per_day
FROM sales
GROUP BY 1,2;

However, as you can see in the data on 2020-12-03 there are no sales at all.
Therefore, the query above is not able to split the daily fix_costs to each country on this day.
To solve this issue I want to modify the query so in case there is a day without a sales_volume the values should be split 50/50.

How do I have to modify the query to achieve this?

mysql sql
1 Answers

If I understand correctly, you. want to allocation 2,000 by each day of the month and for all the countries other than 'NL'.

This is arithmetic with window functions:

  • Use last_day() to get the number of days in the month.
  • Use window functions to calculate the proportion of revenue for each country other than 'NL'.
  • Use conditional logic so 'NL' is special.

As a SELECT query:

select s.*,
       (case when country = 'NL' then 0
             else ((2000 / day(last_day(sales_date))) *
                   sales_volume / nullif(sum(case when country <> 'NL' then sales_volume end) over (partition by sales_date), 0)
                  )
        end) as imputed_fixed_costs
from sales s
order by id;

Note: The 2000 makes the code clearer, but apparently you can input this into the query using:

from sales s cross join
     (select sum(fix_costs) as fixed_costs
      from sales
      where country = 'None'
     ) f

Here is a db<>fiddle.

This is easily incorporated into an update if you really want to change the data, although the example query in the question is a select.

To handle months with no sales:

select s.*,
       (case when country = 'NL'
             then 0
             when sum(case when country <> 'NL' then sales_volume end) over (partition by sales_date) > 0
             then ((2000 / day(last_day(sales_date))) *
                   sales_volume / nullif(sum(case when country <> 'NL' then sales_volume end) over (partition by sales_date), 0)
                  )
             else (2000 / day(last_day(sales_date))) * 1 / sum(country <> 'NL') over (partition by sales_date)
        end) as imputed_fixed_costs
from sales s
order by id;
user_11440350
Related