Swiggy SQL Interview Question | SQL Interview Question no 22 | Daily SQL Practice | Advanced SQL

In This video we have used analytical function like lead and date function like datepart. with the help of these functions and the implementation of those we have achieved the desired output..
The LEAD function is a window (or analytic) function in SQL that allows you to access data from a subsequent row in the same result set without using self-joins or subqueries. It’s extremely useful when you want to compare a value in the current row with a value in the next row.
Syntax:

LEAD(column_name, offset, default_value) OVER (PARTITION BY column_name ORDER BY column_name)

Use Case Scenarios
1. Comparing sales data of the current day with the next day.
2 . Identifying trends like customer churn by comparing current and next row values.

Explanation:

Retrieves the current salary and the salary of the next employee in the sorted list.
If there is no next employee, 0 is returned as the default value.

The DATEPART function extracts a specific part of a date, such as the year, month, day, hour, minute, etc., from a date or datetime value. This is often used for grouping, filtering, or analyzing data based on specific date components.
datepart: The part of the date you want to extract (e.g., year, month, day, hour).
date_expression: The date or datetime value from which the datepart will be extracted.

Use Case Scenarios
Extracting the year from a date to group sales data by year.
Filtering records for a specific month, like extracting data for "January".
Analyzing hourly trends in website traffic.
.
.
.
.

If you found this video helpful, don't forget to like, comment, and subscribe for more SQL interview tips and tutorials.

#SQL #SQLInterviewQuestions #LearnSQL #DataAnalysis #SQLQuery #InterviewTips #Database #Programming
.
.
.
.
--------------Table SCript------------------------------
create table purchases (empid int, purchase_date date)
insert into purchases values (1, '01-08-2024')
insert into purchases values (1 ,'02-08-2024')
insert into purchases values (1 ,'03-08-2024')
insert into purchases values (1, '03-08-2024')
insert into purchases values (1 ,'03-08-2024')
insert into purchases values (2, '01-08-2024')
insert into purchases values (2 ,'03-08-2024')
insert into purchases values (3, '02-08-2024')
insert into purchases values (3 ,'03-08-2024')
insert into purchases values (3, '04-08-2024')
insert into purchases values (4 ,'02-08-2024')

[Savenature]

my approach :
with cte as (select *, row_number() over(order by empid) as rn,
rank() over(partition by empid order by purchase_date) as rnk
from purchases)
select empid
from cte
group by 1, rn-rnk having count(1)=3;

[ANUSHRIDAFE]

my approach : with cte as (select empid, purchase_date, lag(purchase_date, 1, null) over
(partition by empid order by purchase_date )
as lag1,
lag(purchase_date, 2, null) over (partition by empid order by purchase_date )
as lag2 from purchases_1)

select empid from cte where datediff(month, lag1, purchase_date)=1
and datediff(month, lag2, lag1)=1

[vamsivamc]

Sir in second solution we need to count the rows for where difference is 1 to get excactly 3 consecutive days..

[ishamajumdar]

WITH cte AS (
SELECT *,
DAY(purchase_date) - DENSE_RANK() OVER(PARTITION BY empid ORDER BY purchase_date) AS diff
FROM purchases
)
SELECT empid
FROM cte
GROUP BY empid, diff
HAVING COUNT(*) >= 3;

[HARSHRAJ-gpve]

WITH cte AS (
SELECT
purchases.*,
ROW_NUMBER() OVER (PARTITION BY empid ORDER BY purchase_date) AS r1
FROM purchases
),
cte1 AS (
SELECT
empid,
purchase_date,
DAY(purchase_date) AS day1,
r1
FROM cte
), cte2 as(
SELECT
empid,
purchase_date,
CAST(r1 AS SIGNED) - CAST(day1 AS SIGNED) AS r2
FROM cte1
), cte3 as(
select empid, r2, COUNT(*) as c2 FROM cte2 GROUP BY empid, r2 HAVING COUNT(*)>=3
)
select empid FROM cte3 where c2>=3;

[suhasjagadish]

Hello @thedata.techclub i
I think the solution 1 would fail and wouldn't be dynamically applicable to all the dataset ... If there was a record with empid = 2 and purchase_date = 2024-08-10(yyyy-mm-dd) then when we use dense_rank () function we will get 3 as an output for that coulumn and eventually when we subtract it from rownumber() it will give same value as previous data ..This shouldn't happen right ?