I have a table in my PieCloudDB Database that stores daily sales (Some dates may not have data), and I now want to calculate the daily sales growth percentage for consecutive dates.
Suppose I have the following data:
| date | sales |
|---|---|
| 2024-01-01 | 1500 |
| 2024-01-02 | 1400 |
| 2024-01-03 | 1600 |
| 2024-01-04 | 2000 |
| 2024-02-02 | 1100 |
| 2024-02-03 | 1100 |
| 2024-02-04 | 1500 |
I want to get result like this:
| date | sales | daily_growth_percentage |
|---|---|---|
| 2024-01-01 | 1500 | |
| 2024-01-02 | 1400 | -6.67% |
| 2024-01-03 | 1600 | 14.29% |
| 2024-01-04 | 2000 | 25.00% |
| 2024-02-02 | 1100 | |
| 2024-02-03 | 1100 | 0.00% |
| 2024-02-04 | 1500 | 36.36% |
Below is my query:
WITH PreviousSales AS (
SELECT
date,
sales,
LAG(sales) OVER (ORDER BY date) AS previous_sales
FROM Sales
)
SELECT
date,
sales,
CASE
WHEN previous_sales IS NULL THEN NULL
WHEN previous_sales = 0 THEN NULL
ELSE ROUND(((sales - previous_sales)/ previous_sales) * 100, 2)|| '%'
END AS daily_growth_percentage
FROM PreviousSales;
But the results I got were all 0, and the result should not be output on 2024-02-02 (there is no data on 02-01 in the table, so NULL should be output)
| date | sales | daily_growth_percentage |
|---|---|---|
| 2024-01-01 | 1500 | |
| 2024-01-02 | 1400 | 0.00% |
| 2024-01-03 | 1600 | 0.00% |
| 2024-01-04 | 2000 | 0.00% |
| 2024-02-02 | 1100 | 0.00% |
| 2024-02-03 | 1100 | 0.00% |
| 2024-02-04 | 1500 | 0.00% |
How to get the right result?
I found the reason of the problem. The data type of the sales in my PieCloudDB is int. When doing division, the result less than 1 will become 0, after converting (sales - previous_sales) to numeric, the correct result will be output.
Then about the output results on 2024-02-02, just need to partition them by year and month and it will be solved.
So the correct query is as follows:
WITH PreviousSales AS (
SELECT
date,
sales,
LAG(sales) OVER (
PARTITION BY EXTRACT(YEAR FROM date), EXTRACT(MONTH FROM date)
ORDER BY date
) AS previous_sales
FROM Sales
)
SELECT
date,
sales,
CASE
WHEN previous_sales IS NULL THEN NULL
WHEN previous_sales = 0 THEN NULL
ELSE ROUND(((sales - previous_sales)::numeric / previous_sales) * 100, 2)|| '%'
END AS daily_growth_percentage
FROM PreviousSales;