Capgemini SQL Interview Question | Days Since First Purchase (GROUP BY vs Window Functions)

🚀 In this video, we solve a popular SQL interview question step by step!

Learn how to:
✔️ Find the first purchase date per user
✔️ Calculate days since first purchase
✔️ Use GROUP BY and MIN() effectively
✔️ Understand Window Functions (PARTITION BY)

💡 This is a must-know concept for Data Analyst, Data Scientist, and SQL interviews.

📊 Problem Statement
You are given a purchases table with:

USER_ID
PURCHASE_DATE

👉 Task:

Find the first purchase date for each user
Calculate days since that purchase

🧠 Concepts Covered

Aggregations (MIN)
GROUP BY
Window Functions
PARTITION BY
Date Arithmetic in SQL

--Create Table
CREATE TABLE purchases (
USER_ID INT,
PURCHASE_DATE DATE
);

--Insert Data
INSERT INTO purchases (USER_ID, PURCHASE_DATE) VALUES
(1, '2023-01-10'),
(1, '2023-02-15'),
(1, '2023-03-20'),
(2, '2023-05-05'),
(2, '2023-06-01'),
(3, '2024-01-12'),
(3, '2024-02-18'),
(3, '2024-03-25'),
(4, '2025-07-01');

SQL Interview QnAs: https://www.youtube.com/playlist?list=PLL871c9yfLoimvglCSz6UQoqLJcFBJGKo

***Join our Telegram Group for regular updates on Jobs and Data Science materials:
https://t.me/skilltechathon

Follow us on Instagram:
https://www.instagram.com/skilltechathon/

#sql #postgresql #deloitte #deloittejobs #capgeminihiring #mysql #interview #dataengineering #dataanalytics #dataanalysis #capgemini #capgeminijobs #sqlfordataengineer #jobinterview #coding #programming #technicalinterview

Видео Capgemini SQL Interview Question | Days Since First Purchase (GROUP BY vs Window Functions) канала Skill Techath0n