use db1;
DROP TABLE IF EXISTS Orders;
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Amount DECIMAL(10, 2) NOT NULL,
OrderDate DATE NOT NULL,
IsReturn INT NOT NULL -- 0 for New Order, 1 for Return
);
-----
-- ``` insert stmts ----
INSERT INTO Orders (OrderID, Amount, OrderDate, IsReturn) VALUES
(1001, 45.00, '2025-12-01', 0),
(1002, 120.50, '2025-12-01', 0),
(1003, 25.99, '2025-12-02', 0),
(1004, 99.00, '2025-12-02', 0),
(1005, 15.00, '2025-12-02', 1),
(1006, 350.00, '2025-12-03', 0),
(1007, 75.25, '2025-12-03', 0),
(1008, 18.00, '2025-12-03', 0),
(1009, 22.00, '2025-12-03', 1),
(1010, 145.00, '2025-12-04', 0),
(1011, 55.00, '2025-12-04', 0),
(1012, 60.00, '2025-12-04', 0),
(1013, 70.00, '2025-12-04', 0),
(1014, 12.99, '2025-12-04', 0),
(1015, 88.00, '2025-12-05', 0),
(1016, 210.00, '2025-12-05', 0),
(1017, 10.00, '2025-12-05', 1),
(1018, 45.50, '2025-12-06', 0),
(1019, 105.00, '2025-12-06', 0),
(1020, 180.00, '2025-12-07', 0),
(1021, 50.00, '2025-12-07', 0),
(1022, 250.00, '2025-12-07', 1),
(1023, 33.00, '2025-12-08', 0),
(1024, 175.00, '2025-12-08', 0),
(1025, 110.00, '2025-12-09', 0),
(1026, 7.99, '2025-12-09', 0),
(1027, 150.00, '2025-12-10', 0),
(1028, 40.00, '2025-12-10', 0),
(1029, 25.00, '2025-12-10', 1),
(1030, 13.50, '2025-12-10', 0);
select * from orders ;
-- ------- query to find trend sales increasing or not ---
WITH DailyCounts AS (
-- 1. Calculate the total number of orders for each date
SELECT
OrderDate,
COUNT(OrderID) AS DailyOrderCount
FROM
Orders
GROUP BY
OrderDate
)
SELECT
OrderDate,
-- Get the order count from the previous day, using 0 as a default for the first day
LAG(DailyOrderCount, 1, 0) OVER (ORDER BY OrderDate) AS PreviousDayCount,
DailyOrderCount,
-- -----------------------------
-- 2. Compare current count to previous count to determine the trend
CASE
WHEN DailyOrderCount > LAG(DailyOrderCount, 1, 0) OVER (ORDER BY OrderDate) THEN 'Increasing ⬆️'
WHEN DailyOrderCount < LAG(DailyOrderCount, 1, 0) OVER (ORDER BY OrderDate) THEN 'Decreasing ⬇️'
ELSE 'Stable/Same'
END AS Trend
FROM
DailyCounts
ORDER BY
OrderDate;
