Скачать или смотреть SQL Case Study: How I Boost Sales Revenue With LEAD & LAG Window Functions

What is SQL Window Function Lead & Lag? – and How can data scientists use them to drive business results?

In this video, I walk you through a practical case study of data science using the Online Retail Dataset from Kaggle, where we use powerful analytics tool in SQL Window Functions LEAD and LAG to uncover powerful actionable insights that can drive marketing business performance such as boosting Customer Retention.

Specifically, you will learn powerful data scientist skill in business such as:
00:32:00 The importance of Actionable Insights for Marketing Data Science -- the gap between academic and industry data science
00:01:58 How Window Functions Work (especially for lead and lag) -- Basic Theory
00:04:32 Practical Use Case from Kaggle Dataset -- How to use SQL to segment buyers and identify growth opportunities.

Whether you're a beginner or experienced data scientist, this video shows how to bridge the gap between academic data science and real business impact-without complex math or machine learning required. If you want to master SQL for data science and business storytelling, this video is a must-watch.

#StorytellingwithData #SQLforDataAnalysis #DataScientist #SQLCaseStudy

Data Sources:
www.kaggle.com/datasets/vijayuv/onlineretail

SQL Script Part 1:
SELECT a.*,
julianday(a.Next_InvoiceDate)-julianday(a.InvoiceDate) AS DaysUntilNextPurchase,
julianday(a.InvoiceDate)-julianday(a.Previous_InvoiceDate) AS DaysSinceLastPurchase
FROM (
SELECT
CustomerID,InvoiceNo, InvoiceDate,Quantity, UnitPrice,
LAG(InvoiceDate) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate) AS Previous_InvoiceDate,
LEAD(InvoiceDate) OVER (PARTITION BY CustomerID ORDER BY InvoiceDate) AS Next_InvoiceDate
FROM retail_data) a

SQL Script Part 2:
SELECT CustomerID,
SUM(Quantity) As TotalQuantity,
AVG(UnitPrice) as AveragePrice,
AVG(DaysSinceLastPurchase) AS AvgDaysSinceLastPurchase,
AVG(DaysUntilNextPurchase) AS AvgDaysUntilNextPurchase,
CASE
WHEN AVG(DaysUntilNextPurchase) <= 7 THEN 'Frequent Buyer'
ELSE 'Not Frequent Buyer' END AS BuyerSegment
FROM retail_data_2nd_Version
GROUP BY CustomerID

My Linkedin Profile: id.linkedin.com/in/vincent-handara-89955919
Email: [email protected]