voice volumne is irregular do check the mic

Best approach 👍

👍👍

WITH cte1 AS (SELECT *, CASE WHEN Product is null THEN 0 ELSE 1 END flag, ROW_NUMBER() OVER() as rn FROM shop), cte2 AS (SELECT *, SUM(flag) OVER(ORDER BY rn) as cum_flag FROM cte1) SELECT FIRST_VALUE(product) OVER(PARTITION BY cum_flag) as product FROM cte2;

another approach with cte as( select cx_name, concat(product, ',', LEAD(product) over (partition by cx_name order by cx_name)) next_row, ROW_NUMBER() over (partition by cx_name order by cx_name) RN from purchase_wed ) select count(case when next_row = ('Table,Chair') then 1 END) purchased_both, count(case when next_row = ('Table,') or next_row = ('Table,Table') then 1 END) purchased_table, count(case when next_row = ('Chair,') or next_row = ('Chair,Chair') then 1 END) purchased_chair from cte where RN=1

Nice explanation

Instead of dates, try the question with timestamps to avoid discrepancies for the same date orders

another approach with cte as( select *, case when transaction_type='Credit' then SUM(amount) over (partition by customer, transaction_type order by transaction_date rows between 1 preceding and current row) END Balance_1 from balance ) select customer, transaction_date, format(case when transaction_type='Debit' then lag(Balance_1) over (partition by customer order by transaction_date) - amount else Balance_1 END, '#,###,##') Balance from cte order by customer, transaction_type

3

Thanks for the video I was confused between range and rows frames, now a little bit more confident.

Helpful

Nice explanation 👍

The case statement is new to me in the learning stage, but I got this very clearly. Thanks for the detailed explanation.

Helpful Point 🎉

CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Name VARCHAR(50), Department VARCHAR(20), Year INT, Salary int, Position VARCHAR(50) ); INSERT INTO Employees VALUES (1, 'Alice Johnson', 'IT', 2001, 75000, 'Software Engineer'), (2, 'Bob Smith', 'HR', 2003, 65000, 'HR Manager'), (3, 'Carol White', 'Management', 2005, 120000, 'Operations Manager'), (4, 'David Brown', 'Customer Support', 2000, 45000, 'Support Specialist'), (5, 'Eve Black', 'IT', 2002, 85000, 'Network Administrator'), (6, 'Frank Green', 'HR', 2006, 70000, 'Recruiter'), (7, 'Grace Wilson', 'Management', 2007, 95000, 'Project Manager'), (8, 'Hank Taylor', 'Customer Support', 2001, 50000, 'Support Analyst'), (9, 'Ivy Moore', 'IT', 2004, 78000, 'Database Administrator'), (10, 'Jack Clark', 'HR', 2002, 69000, 'Training Coordinator'), (11, 'Kara Lewis', 'Management', 2005, 130000, 'Business Analyst'), (12, 'Liam Hall', 'Customer Support', 2003, 48000, 'Customer Service Representative'), (13, 'Mia Young', 'IT', 2008, 92000, 'DevOps Engineer'), (14, 'Noah King', 'HR', 2009, 71000, 'HR Specialist'), (15, 'Olivia Scott', 'Management', 2000, 140000, 'Senior Manager'), (16, 'Paul Adams', 'Customer Support', 2007, 47000, 'Technical Support Engineer'), (17, 'Quinn Baker', 'IT', 2006, 87000, 'Frontend Developer'), (18, 'Rachel Harris', 'HR', 2008, 68000, 'HR Consultant'), (19, 'Sam Lee', 'Management', 2004, 115000, 'Product Manager'), (20, 'Tina Perez', 'Customer Support', 2005, 49000, 'Customer Success Manager'), (21, 'Umar Collins', 'IT', 2002, 77000, 'Backend Developer'), (22, 'Vera Bell', 'HR', 2003, 64000, 'Compensation Analyst'), (23, 'Will Wright', 'Management', 2007, 105000, 'Marketing Manager'), (24, 'Xena Martinez', 'Customer Support', 2001, 46000, 'Help Desk Technician'), (25, 'Yara Edwards', 'IT', 2009, 93000, 'Security Analyst'), (26, 'Zane Turner', 'HR', 2000, 66000, 'Benefits Coordinator'), (27, 'Aiden Campbell', 'Management', 2006, 125000, 'Finance Manager'), (28, 'Bella Mitchell', 'Customer Support', 2002, 52000, 'Customer Relations Specialist'), (29, 'Caleb Parker', 'IT', 2004, 79000, 'Systems Analyst'), (30, 'Diana Hill', 'HR', 2005, 68000, 'HR Generalist'), (31, 'Ethan Rivera', 'Management', 2003, 110000, 'Sales Manager'), (32, 'Fiona Rogers', 'Customer Support', 2009, 51000, 'Support Technician'), (33, 'George Murphy', 'IT', 2000, 81000, 'Cloud Engineer'), (34, 'Hannah Bailey', 'HR', 2001, 70000, 'Recruitment Officer'), (35, 'Ian Russell', 'Management', 2004, 120000, 'Business Development Manager'), (36, 'Jade Reed', 'Customer Support', 2008, 53000, 'Customer Experience Specialist'), (37, 'Kevin Hughes', 'IT', 2002, 82000, 'IT Manager'), (38, 'Laura Stewart', 'HR', 2007, 75000, 'Employee Relations Specialist'), (39, 'Mason Reid', 'Management', 2006, 135000, 'Chief Operating Officer'), (40, 'Nina Howard', 'Customer Support', 2003, 49000, 'Technical Support Specialist'), (41, 'Oscar Carter', 'IT', 2004, 86000, 'Software Architect'), (42, 'Penny Coleman', 'HR', 2005, 71000, 'Learning and Development Manager'), (43, 'Quentin Cox', 'Management', 2009, 98000, 'Innovation Manager'), (44, 'Ruby Ward', 'Customer Support', 2001, 55000, 'Client Support Specialist'), (45, 'Steve Foster', 'IT', 2000, 89000, 'Mobile Developer'), (46, 'Tiffany Simmons', 'HR', 2006, 76000, 'Diversity and Inclusion Manager'), (47, 'Ulysses James', 'Management', 2005, 140000, 'Chief Executive Officer'), (48, 'Victoria Barnes', 'Customer Support', 2002, 54000, 'Customer Account Manager'), (49, 'Wyatt Fisher', 'IT', 2003, 83000, 'Technical Lead'), (50, 'Xander West', 'HR', 2008, 72000, 'HR Administrator'), (51, 'Yvonne Kelly', 'Management', 2007, 115000, 'Strategic Planner'), (52, 'Zachary Patterson', 'Customer Support', 2009, 50000, 'Support Coordinator'), (53, 'Amber Long', 'IT', 2006, 87000, 'Data Scientist'), (54, 'Brian Hughes', 'HR', 2003, 68000, 'HR Specialist'), (55, 'Catherine Ross', 'Management', 2004, 125000, 'Operations Director'), (56, 'Derek Cook', 'Customer Support', 2005, 53000, 'Client Success Manager'), (57, 'Eva Morgan', 'IT', 2008, 95000, 'Machine Learning Engineer'), (58, 'Fred Ross', 'HR', 2002, 70000, 'Compensation Manager'), (59, 'Gina Powell', 'Management', 2001, 135000, 'General Manager'), (60, 'Henry Scott', 'Customer Support', 2000, 56000, 'Customer Care Representative'), (61, 'Iris Martinez', 'IT', 2009, 92000, 'AI Specialist'), (62, 'Jake Rogers', 'HR', 2007, 74000, 'Employee Engagement Manager'), (63, 'Kayla Nelson', 'Management', 2006, 110000, 'Executive Director'), (64, 'Liam Cook', 'Customer Support', 2003, 52000, 'Technical Account Manager'), (65, 'Mona Brown', 'IT', 2004, 87000, 'IT Support Specialist'), (66, 'Nathaniel Lee', 'HR', 2005, 69000, 'HR Advisor'), (67, 'Olivia Bell', 'Management', 2009, 120000, 'Corporate Strategy Manager'), (68, 'Peter White', 'Customer Support', 2001, 48000, 'Customer Success Associate'), (69, 'Quincy Young', 'IT', 2002, 89000, 'Full Stack Developer'), (70, 'Rachel Lewis', 'HR', 2008, 71000, 'Talent Acquisition Specialist'), (71, 'Sophie Walker', 'Management', 2000, 130000, 'Chief Financial Officer'), (72, 'Tom Harris', 'Customer Support', 2006, 50000, 'Customer Retention Specialist'), (73, 'Uma Collins', 'IT', 2003, 81000, 'Technical Support Engineer'), (74, 'Victor Allen', 'HR', 2004, 74000, 'HR Coordinator'), (75, 'Wendy Young', 'Management', 2007, 145000, 'Chief Marketing Officer'), (76, 'Xavier King', 'Customer Support', 2002, 47000, 'Customer Onboarding Specialist'), (77, 'Yasmine Parker', 'IT', 2001, 84000, 'IT Security Manager'), (78, 'Zoe Stewart', 'HR', 2005, 72000, 'People Operations Manager'), (79, 'Aaron Brown', 'Management', 2003, 100000, 'Business Operations Manager'), (80, 'Blake Reed', 'Customer Support', 2000, 51000, 'Technical Support Analyst'), (81, 'Claire King', 'IT', 2006, 87000, 'Cloud Solutions Architect'), (82, 'Dylan Moore', 'HR', 2009, 67000, 'HR Analyst'), (83, 'Erin Foster', 'Management', 2008, 110000, 'Head of Operations'), (84, 'Fiona Smith', 'Customer Support', 2007, 53000, 'Customer Engagement Specialist'), (85, 'George Carter', 'IT', 2002, 83000, 'Infrastructure Engineer'), (86, 'Holly Brown', 'HR', 2001, 75000, 'Employee Experience Manager'), (87, 'Isaac Collins', 'Management', 2005, 125000, 'Corporate Development Manager'), (88, 'Jenna Thompson', 'Customer Support', 2003, 50000, 'Customer Service Supervisor'), (89, 'Kyle Reed', 'IT', 2004, 85000, 'DevOps Specialist'), (90, 'Lily Parker', 'HR', 2006, 72000, 'HR Business Partner'), (91, 'Mason Mitchell', 'Management', 2007, 130000, 'Vice President'), (92, 'Nina Baker', 'Customer Support', 2008, 49000, 'Support Desk Manager'), (93, 'Owen Harris', 'IT', 2000, 89000, 'Senior Developer'), (94, 'Paula Green', 'HR', 2009, 68000, 'HR Operations Manager'), (95, 'Quentin Ross', 'Management', 2004, 145000, 'Chief Technology Officer'), (96, 'Rachel Cooper', 'Customer Support', 2001, 54000, 'Customer Service Manager'), (97, 'Steven White', 'IT', 2005, 91000, 'Lead Developer'), (98, 'Tara King', 'HR', 2002, 71000, 'HR Lead'), (99, 'Uma Scott', 'Management', 2006, 120000, 'Executive Manager'), (100, 'Victor Young', 'Customer Support', 2003, 48000, 'Support Engineer'), (101, 'Sundar Pichai',null,1999,null,'CEO');

A4 : select * from student_result where marks > 80; A5 : select * from student_result where marks >= 85; A9 : select student_name, marks from student_result where marks < 50 or marks > 80 ;