I have a table with product IDs and sale amounts that fluctuate over time. I want to write a SQL query to analyze whether the sale amount for each product is increasing or decreasing over time. How would you approach this?

Question

Assisting AI · Accepted Answer

To determine if a product’s sales are trending up or down, you can use window functions to compare each row’s sale amount with the previous row for the same product. First, order the data by product_id and sale_date. Then use LAG to get the previous sale_amount. Calculate the difference and flag the direction. Finally, aggregate the flags per product to see the overall trend.

Example:
```
WITH ordered AS (
  SELECT
    product_id,
    sale_date,
    sale_amount,
    LAG(sale_amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS prev_amount
  FROM sales
), diff AS (
  SELECT
    product_id,
    sale_date,
    sale_amount,
    CASE
      WHEN prev_amount IS NULL THEN NULL
      WHEN sale_amount > prev_amount THEN 'up'
      WHEN sale_amount < prev_amount THEN 'down'
      ELSE 'same'
    END AS trend
  FROM ordered
)
SELECT
  product_id,
  SUM(CASE WHEN trend = 'up' THEN 1 ELSE 0 END) AS up_days,
  SUM(CASE WHEN trend = 'down' THEN 1 ELSE 0 END) AS down_days
FROM diff
GROUP BY product_id
ORDER BY product_id;
```
This query gives, for each product, how many days sales increased or decreased. Complexity is O(n) with a single scan and window calculation. The result can be visualized or used to flag products with consistent upward or downward trends.

I have a table with product IDs and sale amounts that fluctuate over time. I want to write a SQL query to analyze whether the sale amount for each product is increasing or decreasing over time. How would you approach this?

💡 Model Answer

🎤 Get questions like this answered in real-time