SQL Tutorial 31: Write a Query to Find Consecutive Share Price Drops

Watch the full SQL playlist here: https://www.youtube.com/playlist?list=PL_neLGJ5LPUzPIg96tf7rXb57D6-3n5Pq SQL Challenge: For each stock, find the first date when its price dropped for the second consecutive day, a classic logic-heavy SQL interview problem that tests your understanding of trends and sequence detection. What You’ll Learn in This Video: 1. How to detect back-to-back conditions in a single table 2. Using window functions like ROW_NUMBER() and LAG() 3. Alternative solution using ROW_NUMBER + self joins 4. Clean and compatible solution for SQL Server Sample Table Schema: CREATE TABLE StockPrices ( PriceID INT PRIMARY KEY, StockSymbol VARCHAR(10), Price DECIMAL(10, 2), PriceDate DATE ); INSERT INTO StockPrices (PriceID, StockSymbol, Price, PriceDate) VALUES -- AAPL (1, 'AAPL', 150.00, '2023-01-01'), (2, 'AAPL', 152.00, '2023-01-02'), (3, 'AAPL', 149.00, '2023-01-03'), (4, 'AAPL', 148.00, '2023-01-04'), (5, 'AAPL', 151.00, '2023-01-05'), (6, 'AAPL', 150.00, '2023-01-06'), (7, 'AAPL', 149.00, '2023-01-07'), (8, 'AAPL', 147.00, '2023-01-08'), -- GOOG (9, 'GOOG', 100.00, '2023-01-01'), (10, 'GOOG', 101.00, '2023-01-02'), (11, 'GOOG', 102.00, '2023-01-03'), (12, 'GOOG', 103.00, '2023-01-04'), (13, 'GOOG', 99.00, '2023-01-05'), (14, 'GOOG', 98.00, '2023-01-06'), (15, 'GOOG', 97.00, '2023-01-07'), (16, 'GOOG', 96.00, '2023-01-08'), -- MSFT (17, 'MSFT', 200.00, '2023-01-01'), (18, 'MSFT', 198.00, '2023-01-02'), (19, 'MSFT', 197.00, '2023-01-03'), (20, 'MSFT', 199.00, '2023-01-04'), (21, 'MSFT', 198.00, '2023-01-05'), (22, 'MSFT', 196.00, '2023-01-06'), (23, 'MSFT', 195.00, '2023-01-07'); Expected Output: StockSymbol | SecondConsecutiveDropDate ------------|---------------------------- AAPL | 2023-01-04 GOOG | 2023-01-06 MSFT | 2023-01-03 Perfect for: SQL interview prep, Intermediate SQL learners and Data analysts & engineers Don’t forget to like, subscribe, and comment if this helped you! Hashtags: #SQL #SQLQuery #SQLInterview #LearnSQL #DataScience #TechInterview #Programming #SQLServer #WindowFunctions #CodeChallenge