Blinkit Data Analyst SQL Assessment

Описание: In this video we will solve a Blinkit Data Analyst SQL Assessment. This is one of the complex SQL question. You can expect these kind of SQL questions in startups and product based companies interviews.

Practice more SQL and Spark questions here : https://www.namastesql.com/coding-pro...

Scripts:
CREATE TABLE orders (
customer_id INT,
order_date DATE,
coupon_code VARCHAR(50)
);

TRUNCATE TABLE Orders;

-- ✅ Customer 1: First order in Jan, valid pattern
INSERT INTO Orders VALUES (1, '2025-01-10', NULL);
INSERT INTO Orders VALUES (1, '2025-02-05', NULL);
INSERT INTO Orders VALUES (1, '2025-02-20', NULL);
INSERT INTO Orders VALUES (1, '2025-03-01', NULL);
INSERT INTO Orders VALUES (1, '2025-03-10', NULL);
INSERT INTO Orders VALUES (1, '2025-03-15', 'DISC10'); -- last order with coupon ✅

-- ✅ Customer 2: First order in Feb, valid pattern
INSERT INTO Orders VALUES (2, '2025-02-02', NULL); -- Month1 = 1
INSERT INTO Orders VALUES (2, '2025-02-05', NULL); -- Month1 = 1
INSERT INTO Orders VALUES (2, '2025-03-05', NULL); -- Month2 = 2
INSERT INTO Orders VALUES (2, '2025-03-18', NULL);
INSERT INTO Orders VALUES (2, '2025-03-20', NULL); -- Month2 = 2
INSERT INTO Orders VALUES (2, '2025-03-22', NULL);
INSERT INTO Orders VALUES (2, '2025-04-02', NULL); -- Month3 = 3
INSERT INTO Orders VALUES (2, '2025-04-10', NULL);
INSERT INTO Orders VALUES (2, '2025-04-15', 'DISC20'); -- last order with coupon ✅
INSERT INTO Orders VALUES (2, '2025-04-16', NULL); -- Month3 = 3
INSERT INTO Orders VALUES (2, '2025-04-18', NULL);
INSERT INTO Orders VALUES (2, '2025-04-20', 'DISC20'); -- last order with coupon ✅

-- ❌ Customer 3: First order in March but wrong multiples
INSERT INTO Orders VALUES (3, '2025-03-05', NULL); -- Month1 = 1
INSERT INTO Orders VALUES (3, '2025-04-10', NULL); -- Month2 should have 2, but only 1 ❌
INSERT INTO Orders VALUES (3, '2025-05-15', 'DISC30');

-- ❌ Customer 4: First order in Feb but missing March (gap)
INSERT INTO Orders VALUES (4, '2025-02-01', NULL); -- Month1
INSERT INTO Orders VALUES (4, '2025-04-05', 'DISC40'); -- Skipped March ❌

-- ❌ Customer 5: Valid multiples but last order has no coupon
INSERT INTO Orders VALUES (5, '2025-01-03', NULL); -- M1 = 1
INSERT INTO Orders VALUES (5, '2025-02-05', NULL); -- M2 = 2
INSERT INTO Orders VALUES (5, '2025-02-15', NULL);
INSERT INTO Orders VALUES (5, '2025-03-01', NULL); -- M3 = 3
INSERT INTO Orders VALUES (5, '2025-03-08', 'DISC50'); -- coupon mid
INSERT INTO Orders VALUES (5, '2025-03-20', NULL); -- last order no coupon ❌

-- ❌ Customer 6: Skips month 2, should be excluded
INSERT INTO Orders VALUES (6, '2025-01-05', NULL); -- Month1 = 1 order
-- (no orders in Feb, so Month2 is missing ❌)
INSERT INTO Orders VALUES (6, '2025-03-02', NULL); -- Month3 = 1st order
INSERT INTO Orders VALUES (6, '2025-03-15', NULL); -- Month3 = 2nd order
-- Jump to May (Month5 relative to Jan)
INSERT INTO Orders VALUES (6, '2025-05-05', NULL);
INSERT INTO Orders VALUES (6, '2025-05-10', NULL);
INSERT INTO Orders VALUES (6, '2025-05-25', 'DISC60'); -- Last order with coupon


Zero to hero(Advance) SQL Aggregation:
   • All About SQL Aggregations | SQL Advance |...  

Most Asked Join Based Interview Question:
   • Most Asked SQL JOIN based Interview Questi...  

Solving 4 Trick SQL problems:
   • Solving 4 Tricky SQL Problems  

Data Analyst Spotify Case Study:
   • Data Analyst Spotify Case Study | SQL Inte...  

Top 10 SQL interview Questions:
   • Top 10 SQL interview Questions and Answers...  

Interview Question based on FULL OUTER JOIN:
   • SQL Interview Question Based on Full Outer...  

Playlist to master SQL :
   • Complex SQL Questions for Interview Prepar...  

Rank, Dense_Rank and Row_Number:
   • RANK, DENSE_RANK, ROW_NUMBER SQL Analytica...  

#sql #dataanalytics