Iterate through timestamps and calculate differenc…

DB-Fiddle:

/* Sales */
CREATE TABLE sales (
    id int auto_increment primary key,
    time_stamp DATE,
    product VARCHAR(255),
    sales_quantity INT
);

INSERT INTO sales
(time_stamp, product, sales_quantity)
VALUES 
("2020-01-14", "Product_A", "100"),
("2020-01-14", "Product_B", "300"),

("2020-01-15", "Product_B", "100"),
("2020-01-15", "Product_C", "350"),

("2020-01-16", "Product_B", "130"),
("2020-01-16", "Product_C", "350"),
("2020-01-16", "Product_D", "670"),

("2020-01-17", "Product_C", "130"),
("2020-01-17", "Product_D", "980"),
("2020-01-17", "Product_E", "700"),
("2020-01-17", "Product_F", "450");


/* Products */
CREATE TABLE products (
    id int auto_increment primary key,
    product VARCHAR(255)
);

INSERT INTO products 
(product)
VALUES 
("Product_B"),
("Product_D");

Expected Result:

time_stamp      difference
2020-01-15        -200     = (100)-(200)
2020-01-16         700     = (130+670) - (100)
2020-01-17         180     = (980) - (130+670)

I want to calculate the difference of the sales_quantity of different timestamps of products that are also existing in the table products.

Therefore, I created this query:

WITH cte AS (SELECT
             product
             FROM products
             GROUP BY 1)

SELECT
MAX(t1.time_stamp_02),
(SUM(t1.sales_quantity_02) - SUM(t1.sales_quantity_01)) AS difference
FROM

  (SELECT
  time_stamp AS time_stamp_01,
  0 AS time_stamp_02,
  SUM(sales_quantity) AS sales_quantity_01,
  0 AS sales_quantity_02
  FROM sales
  WHERE time_stamp BETWEEN '2020-01-14' AND '2020-01-14'
  AND product IN (SELECT product FROM cte)

  UNION ALL

  SELECT
  0 AS time_stamp_01,
  time_stamp AS time_stamp_02,
  0 AS sales_quantity_01,
  SUM(sales_quantity) AS sales_quantity_02
  FROM sales
  WHERE time_stamp BETWEEN '2020-01-15' AND '2020-01-15'
  AND product IN (SELECT product FROM cte)) t1

The query itself works but my issue is that I can only query two timestamps separately.

Therefore, I am wondering if there is a solution that iterates through all the timestamps and gives me the expected result?

Answer

You can try the below – DEMO

select * from
(
select time_stamp,totalq-lag(totalq) over(order by time_stamp) as val
from
(
select time_stamp,sum(sales_quantity) as totalq
from products p join sales s on p.product=s.product
group by time_stamp
)A)final where val is not null

Iterate through timestamps and calculate difference between them

Advertisement

Answer