Search code examples
mysqlsqldatecountwindow-functions

mysql query to find daily increase or decrease

I'm new to mysql. I have a table as below. It has stock level for different SKUs stored every day. I am trying to write a query that outputs how many SKUs have increased stocks and how many reduced stock levels on a daily basis over a number of days. My main issue is finding the difference in stock levels daily between any two dates and classifying it as increase or decrease. The table does not store the amount of increase or decrease daily. Any help/direction will be greatly appreciated.

Table:

+------------+---------+-------+
| Date       | SKU     | Stock |
+------------+---------+-------+
| 2020-03-23 | SKU1001 | 23149 |
| 2020-03-23 | SKU1002 | 29218 |
| 2020-03-23 | SKU1003 | 14827 |
| 2020-03-23 | SKU1004 |  8852 |
| 2020-03-23 | SKU1005 | 47362 |
| 2020-03-23 | SKU1006 |  3572 |
| 2020-03-23 | SKU1007 |  8744 |
| 2020-03-23 | SKU1008 | 22788 |
| 2020-03-23 | SKU1009 | 41897 |
| 2020-03-23 | SKU1010 | 28245 |
| 2020-03-22 | SKU1001 | 18326 |
| 2020-03-22 | SKU1002 | 23123 |
| 2020-03-22 | SKU1003 | 48501 |
| 2020-03-22 | SKU1004 | 44070 |
| 2020-03-22 | SKU1005 |  3160 |
| 2020-03-22 | SKU1006 | 46216 |
| 2020-03-22 | SKU1007 |  1620 |
| 2020-03-22 | SKU1008 |  3314 |
| 2020-03-22 | SKU1009 | 32254 |
| 2020-03-22 | SKU1010 |  1442 |
| 2020-03-19 | SKU1001 | 40992 |
| 2020-03-19 | SKU1002 | 31477 |
| 2020-03-19 | SKU1003 | 22976 |
| 2020-03-19 | SKU1004 | 26858 |
| 2020-03-19 | SKU1005 | 32397 |
| 2020-03-19 | SKU1006 | 37801 |
| 2020-03-19 | SKU1007 | 19530 |
| 2020-03-19 | SKU1008 | 35202 |
| 2020-03-19 | SKU1009 | 11723 |
| 2020-03-19 | SKU1010 | 21201 |
| 2020-03-18 | SKU1001 |  7449 |
| 2020-03-18 | SKU1002 | 10404 |
| 2020-03-18 | SKU1003 | 34944 |
| 2020-03-18 | SKU1004 |  5696 |
| 2020-03-18 | SKU1005 | 14732 |
| 2020-03-18 | SKU1006 |  9916 |
| 2020-03-18 | SKU1007 | 46623 |
| 2020-03-18 | SKU1008 |  6755 |
| 2020-03-18 | SKU1009 | 42848 |
| 2020-03-18 | SKU1010 |  5209 |
| 2020-03-17 | SKU1001 | 31777 |
| 2020-03-17 | SKU1002 | 36504 |
| 2020-03-17 | SKU1003 | 43737 |
| 2020-03-17 | SKU1004 | 27706 |
| 2020-03-17 | SKU1005 | 12099 |
| 2020-03-17 | SKU1006 | 39922 |
| 2020-03-17 | SKU1007 |  4897 |
| 2020-03-17 | SKU1008 | 14773 |
| 2020-03-17 | SKU1009 | 20108 |
| 2020-03-17 | SKU1010 | 40094 |
| 2020-03-16 | SKU1001 | 15459 |
| 2020-03-16 | SKU1002 | 39511 |
| 2020-03-16 | SKU1003 | 13586 |
| 2020-03-16 | SKU1004 | 29648 |
| 2020-03-16 | SKU1005 | 41381 |
| 2020-03-16 | SKU1006 | 27868 |
| 2020-03-16 | SKU1007 |  4220 |
| 2020-03-16 | SKU1008 | 22182 |
| 2020-03-16 | SKU1009 |  9079 |
| 2020-03-16 | SKU1010 | 33130 |
| 2020-03-15 | SKU1001 | 29597 |
| 2020-03-15 | SKU1002 | 41033 |
| 2020-03-15 | SKU1003 | 40937 |
| 2020-03-15 | SKU1004 | 34551 |
| 2020-03-15 | SKU1005 |  7283 |
| 2020-03-15 | SKU1006 | 40625 |
| 2020-03-15 | SKU1007 |  7935 |
| 2020-03-15 | SKU1008 | 30623 |
| 2020-03-15 | SKU1009 | 27591 |
| 2020-03-15 | SKU1010 |  7633 |
| 2020-03-12 | SKU1001 | 21712 |
| 2020-03-12 | SKU1002 | 11933 |
| 2020-03-12 | SKU1003 | 25913 |
| 2020-03-12 | SKU1004 | 33388 |
| 2020-03-12 | SKU1005 | 44811 |
| 2020-03-12 | SKU1006 | 10177 |
| 2020-03-12 | SKU1007 |  4748 |
| 2020-03-12 | SKU1008 | 48676 |
| 2020-03-12 | SKU1009 | 44767 |
| 2020-03-12 | SKU1010 | 33986 |
| 2020-03-11 | SKU1001 |  9156 |
| 2020-03-11 | SKU1002 | 48079 |
| 2020-03-11 | SKU1003 |  8815 |
| 2020-03-11 | SKU1004 | 15756 |
| 2020-03-11 | SKU1005 |  4446 |
| 2020-03-11 | SKU1006 | 40009 |
| 2020-03-11 | SKU1007 | 15591 |
| 2020-03-11 | SKU1008 | 12904 |
| 2020-03-11 | SKU1009 | 34635 |
| 2020-03-11 | SKU1010 | 20042 |
| 2020-03-10 | SKU1001 | 11811 |
| 2020-03-10 | SKU1002 | 26257 |
| 2020-03-10 | SKU1003 | 11387 |
| 2020-03-10 | SKU1004 | 30888 |
| 2020-03-10 | SKU1005 | 12192 |
| 2020-03-10 | SKU1006 |  5236 |
| 2020-03-10 | SKU1007 | 26115 |
| 2020-03-10 | SKU1008 | 34821 |
| 2020-03-10 | SKU1009 | 15294 |
| 2020-03-10 | SKU1010 |  3344 |
+------------+---------+-------+

Output required:

Date        Decrease    Increase
2020-03-10                10
2020-03-11         6       4
2020-03-12         3       7
2020-03-15         4       6
2020-03-16         8       2
2020-03-17         4       6
2020-03-18         7       3
2020-03-19         3       7
2020-03-22         6       4
2020-03-23         3       7
Solution 
  • WITH
cte1 AS ( SELECT DISTINCT `date`
          FROM test ),
cte2 AS ( SELECT DISTINCT sku
          FROM test ),
cte3 AS ( SELECT cte1.`date`, 
                 cte2.sku, 
                 COALESCE( stock,
                           FIRST_VALUE(stock) OVER (PARTITION BY cte2.sku 
                                                    ORDER BY cte1.`date` DESC
                                                    ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING), 
                           0) stock
          FROM cte1
          CROSS JOIN cte2
          LEFT JOIN test ON cte1.`date` = test.`date`
                        AND cte2.sku = test.sku ),
cte4 AS ( SELECT `date`, 
                 sku, 
                 stock,
                 COALESCE( LAG(stock) OVER (PARTITION BY sku
                                            ORDER BY `date`),
                           0 ) lag_stock 
          FROM cte3)
SELECT `date`,
       SUM(stock < lag_stock) Decrease,
       SUM(stock > lag_stock) Increase
FROM cte4
GROUP BY `date`;

    fiddle

    If the row for some particular sku for some date is absent then the value for the closest previous date is used.

    If some intermediate date value is absent (like 2020-03-20 and 2020-03-21 in the example data) then this date is absent in output too. It may be added synthetically with both statistic columns values equal to zero.