#SQL #LEAD VS Self Join #time series foundation #percentage of change

#LEAD VA SELF JOIN #LEAD explanation

-- 1️⃣ Create the sales table
CREATE TABLE daily_sales (
date DATE PRIMARY KEY,
sales INT
);

-- 2️⃣ Insert sample data
INSERT INTO daily_sales (date, sales) VALUES
('2025-07-01', 125), ('2025-07-02', 132), ('2025-07-03', 119), ('2025-07-04', 140),
('2025-07-05', 138), ('2025-07-06', 144), ('2025-07-07', 135), ('2025-07-08', 127),
('2025-07-09', 130), ('2025-07-10', 137), ('2025-07-11', 142), ('2025-07-12', 129),
('2025-07-13', 143), ('2025-07-14', 131), ('2025-07-15', 128), ('2025-07-16', 133),
('2025-07-17', 145), ('2025-07-18', 124), ('2025-07-19', 138), ('2025-07-20', 130),
('2025-07-21', 136), ('2025-07-22', 120), ('2025-07-23', 134), ('2025-07-24', 127),
('2025-07-25', 141), ('2025-07-26', 132), ('2025-07-27', 139), ('2025-07-28', 126),
('2025-07-29', 137), ('2025-07-30', 143), ('2025-07-31', 129), ('2025-08-01', 131),
('2025-08-02', 134), ('2025-08-03', 128), ('2025-08-04', 136), ('2025-08-05', 140),
('2025-08-06', 124), ('2025-08-07', 138), ('2025-08-08', 130), ('2025-08-09', 127),
('2025-08-10', 145), ('2025-08-11', 139), ('2025-08-12', 135), ('2025-08-13', 129),
('2025-08-14', 132), ('2025-08-15', 126), ('2025-08-16', 141), ('2025-08-17', 123),
('2025-08-18', 133), ('2025-08-19', 137);

-- 3️⃣ Self-Join approach to get next-day sales and percentage change
SELECT
a.date,
a.sales,
b.sales AS next_sales,

FROM daily_sales a
JOIN daily_sales b ON b.date = DATEADD(DAY, 1, a.date);

-- 4️⃣ LEAD() window function to do the same in a simpler way
SELECT
date,
sales,
LEAD(sales) OVER (ORDER BY date) AS next_sales,
(CAST(LEAD(sales) OVER (ORDER BY date) AS FLOAT) - sales) * 100.0 / sales AS pct_change
FROM daily_sales;

Видео #SQL #LEAD VS Self Join #time series foundation #percentage of change канала The BI team