I am a Data Engineer at Amazon. I have 13 years of experience into Data Analytics/Engineering . During these years I have worked on multiple tools and technologies.
SQL is one technology which I have constantly worked on through out these 11 years and I just love writing SQLs each and everyday. I have decided to share my knowledge via youtube videos. Videos will be mix of concepts and scenerio based questions.If you want to fall in love with SQL , do subscribe to the channel. I will be sharing content around SQL, Tableau and Python.
Business Enquiries : [email protected]
Пікірлер
select user_id, count(created_at) purchase_days, count(distinct product_id) distinct_products , count(distinct created_at) distinct_created_date from marketing_campaign where user_id in (11,14,25) group by user_id having count(distinct created_at) > 1 and count(distinct product_id) = count(created_at); Also ankit your solution will fail, if the next day we also have records with new product + repeating products. In this case ideally that user id should not be included in result set but as per your solution it will be included.
Very intersting one. Thanks for the logical thinking
Very intersting one
Ekdum se mazaa aa gaya iss video me.
Cheers 🍻
Your ability to divide complex problems into simpler ones is phenomenal. Thank you so much for the sql content. keep going
Cheers
Thank you so much, it made my topic crystal clear :-)
with k as ( select * , dense_rank() over (partition by emp_id order by [time] desc) as rnk from #hospital ) select emp_id from k where rnk = 1 and [action] = 'in'
My solution : with t1 as ( select Name, case when attacker_outcome=1 then attacker_king else defender_king end as winner_king, region from battle), t2 as ( select region,winner_king, k.house , from t1 inner join king k on t1.winner_king=k.k_no ) select * from ( select t2.region, t2.house, count(*) as no_of_wins ,dense_rank() over (partition by Region order by count(*) desc) as rank from t2 group by t2.region,t2.house ) a where a.rank =1
WITH CTE AS ( SELECT date_value, state, ROW_NUMBER() OVER (PARTITION BY state ORDER BY date_value) - ROW_NUMBER() OVER (ORDER BY date_value) AS grp FROM tasks ) SELECT MIN(date_value) AS start_date, MAX(date_value) AS end_date, state FROM CTE GROUP BY state, grp ORDER BY start_date;
my solution with cte as( select * , left(seat,1) as row_id, cast(SUBSTRING(seat,2,2) as int) as seat_id from movie) , cte2 as( select * , sum(case when occupancy=0 then 1 else 0 end) over(partition by row_id order by seat_id rows between current row and 3 following) as sum_1 , sum(case when occupancy=0 then 1 else 0 end) over(partition by row_id order by seat_id rows between 1 preceding and 2 following) as sum_2 , sum(case when occupancy=0 then 1 else 0 end) over(partition by row_id order by seat_id rows between 2 preceding and 1 following) as sum_3 , sum(case when occupancy=0 then 1 else 0 end) over(partition by row_id order by seat_id rows between 3 preceding and current row) as sum_4 from cte) select seat from cte2 where sum_1=4 or sum_2=4 or sum_3=4 or sum_4=4
with cte as (SELECT teamid, sum(qualify) as no_qualified from (SELECT *, case when criteria1='Y' and criteria2='Y' then 1 else 0 end as qualify from Ameriprise_LLC) group by teamid) SELECT *, case when criteria1='Y' and criteria2='Y' and no_qualified >=2 then 'yes' else 'no' end as finaliy from Ameriprise_LLC a inner join cte c on a.teamID=c.teamID
Hi ankit. Here is my solution - with cte1 as( select region,case when attacker_outcome=1 then attacker_king else defender_king end as battle_winner from battle ), cte2 as( select cte1.region,king.house,count(*) as no_of_wins, rank() over(partition by cte1.region order by count(*) desc) as rn from cte1 inner join king on cte1.battle_winner=king.k_no group by cte1.region,king.house ) select * from cte2 where rn=1
nice approach regarding nsertion of dummy record. here is my approach : WITH user_platform_usage AS ( SELECT spend_date, user_id, SUM(CASE WHEN platform = 'desktop' THEN 1 ELSE 0 END) AS desktop_count, SUM(CASE WHEN platform = 'mobile' THEN 1 ELSE 0 END) AS mobile_count, SUM(CASE WHEN platform = 'desktop' THEN amount ELSE 0 END) AS desktop_amount, SUM(CASE WHEN platform = 'mobile' THEN amount ELSE 0 END) AS mobile_amount FROM user_spend GROUP BY 1, 2 ), platform_summary AS ( SELECT spend_date, 'desktop' AS platform, SUM(desktop_amount) AS total_amount, COUNT(CASE WHEN desktop_count > 0 AND mobile_count = 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 UNION ALL SELECT spend_date, 'mobile' AS platform, SUM(mobile_amount) AS total_amount, COUNT(CASE WHEN mobile_count > 0 AND desktop_count = 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 UNION ALL SELECT spend_date, 'both' AS platform, SUM(desktop_amount + mobile_amount) AS total_amount, COUNT(CASE WHEN desktop_count > 0 AND mobile_count > 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 ) SELECT spend_date, platform, total_amount, total_users FROM platform_summary ORDER BY 1, 2 desc
My approach for additional assignment sir: SELECT ticket_id , create_date, resolved_date, datediff(day,create_date,resolved_date) as act_days, datediff(day,create_date,resolved_date) - 2*datediff(week,create_date,resolved_date) - holiday as business_days from (select ticket_id , create_date, resolved_date, count(holiday_date) as holiday from ticket tk left join ( select * from holiday WHERE DAYNAME(TO_DATE(holiday_date)) NOT IN ('Sat', 'Sun') )hl on hl.holiday_date between tk.create_date and tk.resolved_date group by 1,2,3 order by tk.ticket_id);
But what when the year changes and you take difference between 1st week and 52th?
Datediff function takes care of it.
Best question to practice Non-Equi Join, Bhaiya can you please provide dataset, so we can do handson.
in my sql server datepart and dateadd function are not supported instead we can use DATE_ADD and DAYSOFWEEK . THIS IS THE CODE DELIMITER $$ create function get_nth_sunday(N INT,CUR_DATE DATE) RETURNS DATE deterministic BEGIN RETURN ( SELECT DATE_ADD( DATE_ADD(CUR_DATE,INTERVAL 8-dayofweek(CUR_DATE) DAY) , INTERVAL N-1 WEEK)) ; END ; $$ DELIMITER
In MySql workbench ------->For question 1 #Method1 with cte as ( select employee_id,date(activity_time) as date ,max(activity_time) as max, min(activity_time) as min from swipe group by employee_id,date(activity_time) ) select employee_id,Date, time(max-min) as total_hours from cte #Method2 with cte as ( select employee_id,date(activity_time) as date, max(activity_time) over(partition by employee_id,date(activity_time)) as max, min(activity_time) over(partition by employee_id,date(activity_time)) as min from swipe ) select distinct employee_id, date ,hour(max-min) as working_hours from cte -------> For question 2 with cte as ( select *, lead(activity_time) over(partition by employee_id,date(activity_time)) as lead1 from swipe ) , cte2 as ( select *, timestampdiff(hour,activity_time,lead1) as productive_hour from cte where activity_type='login' ) select employee_id, date(activity_time) as date, sum(productive_hour) as total_productive_hours from cte2 group by employee_id, date(activity_time) order by date(activity_time)
with cte as (select *, lag(cases,1,0) over(partition by city order by days) prev_cases from covid) , cte1 as (select *,case when prev_cases < cases then 1 else 0 end grp from cte) select distinct cte1.city from cte1 group by cte1.city having(sum(grp)) = count(*) ;
my solution using rank window function with cte as( select *, rank() over(partition by city_id order by business_date) as rn from business_city) select YEAR(business_date) as year , count(case when rn=1 then city_id else null end) as cnt_new_city from cte group by YEAR(business_date)
Great explaination🎉
WITH CTE1 AS ( SELECT HALL_ID,DAY(START_DATE) StartDate , DAY(END_DATE) EndDate FROM hall_events) , CTE2 AS (SELECT *, LAG(ENDDATE,1,STARTDATE) OVER (PARTITION BY HALL_ID ORDER BY STARTDATE) [DATE] ,CASE WHEN LAG(ENDDATE,1,STARTDATE) OVER (PARTITION BY HALL_ID ORDER BY STARTDATE) >= STARTDATE THEN 0 ELSE 1 END AS FLAGS FROM CTE1 ) SELECT Hall_ID, MIN(STARTDATE) ,MAX(ENDDATE) FROM CTE2 GROUP BY HALL_ID ,FLAGS ORDER BY HALL_I
your mail id..?
[email protected]
With base_table as ( select id, Value , substring(formula,1,1) as row_1, substring(formula,2,1) as operatorr, substring(formula,3,1) as row_2 from input_ank ) select b.id as b, b.value, a.operatorr, c.value, CASE a.operatorr WHEN '+' THEN b.value + c.value WHEN '-' THEN b.value - c.value END AS result from base_table b inner join base_table a on b.id = a.row_1 left join input_ank c on c.id = a.row_2
just wanted to know whats the experience range for these kind of questions ? I mean whats the experience of the candidate interviewed
Amazing Amazing seriously Amazing .... 👏 👏 👏 Thank you so mych
with cte1 as (select *,count( platform) over (partition by spend_Date,user_Id) as r1 from spending) select * from ( select spend_Date,platform,sum(amount),count(distinct user_id) from cte1 where r1=1 group by 1,2 union all select spend_Date,'both' as platform,sum(amount),count(distinct user_id) from cte1 where r1=2 group by 1,2) b
Hi everyone. I solved using self join please check it. with cte1 as(select*from stadium where no_of_people>100 ),cte2 as( select a.*,b.id as id_1,b.visit_date as bv,b.no_of_people as next_number,c.id as id_2,c.visit_date as cv,c.no_of_people as second_next_number from cte1 a join cte1 b on a.visit_date=dateadd(day,1,b.visit_date) join cte1 c on a.visit_date=dateadd(day,2,c.visit_date) ) select id,visit_date,no_of_people from cte2 union select id_1 as id ,bv,next_number from cte2 union select id_2 as id,cv,second_next_number from cte2
SELECT f1.person, f2.person FROM family f1 left join family f2 on SUBSTR(f1.person, 2, LENGTH(f1.person) - 1) = SUBSTR(f2.person, 2, LENGTH(f2.person) - 1) and f1.person <> f2.person where f1.type <> 'Child';
here is my solution that includes if holiday date is on weekend with cte as (select * from tickets t left join holidays h on h.holiday_date between t.create_date and t.resolved_date) select ticket_id,create_date,resolved_date,timestampdiff(day,create_date,resolved_date ) - 2* (timestampdiff(week,create_date,resolved_date))- count(holiday_date)+ sum(case when dayofweek(holiday_date) in ('saturday','sunday') then 1 else 0 end) as business_days from cte group by ticket_id,create_date,resolved_date;
⚡⚡Faster exists operator than two joins ⚡⚡ select distinct f.user_id,l.page_id from friends f inner join likes l on l.user_id= f.friend_id where not exists (select 1 from likes l1 where l1.user_id = f.user_id and l1.page_id = l.page_id) order by 1
please share the link for Microsoft Sql server management studio installation.
Simple and easy way : with t1 as ( select salary from ( select salary,dept_id from emp_salary group by salary,dept_id having count(*) > 1)) select * from emp_salary where salary in (select * from t1)
Easy and short solution : with t1 as ( select emp_id, max(time) as latest_action_time from hospital group by emp_id ) select h.emp_id, h.action from hospital h inner join t1 as t on h.time=t.latest_action_time and h.emp_id=t.emp_ID where h.action='in'
My solution : with cte as ( select ticket_id,create_date,resolved_date , count(holiday_date) as holiday_count from dummy_tickets t left join holidays h on h.holiday_date between t.create_date and t.resolved_date where h.holiday_date is not null group by ticket_id,create_date,resolved_date) select t.ticket_id, t.create_date, t.resolved_date , c.holiday_count , datediff(day, t.create_date,t.resolved_date),datediff(day, t.create_date,t.resolved_date) - 2*datediff(week, t.create_date,t.resolved_date) - case when c.holiday_count is null then 0 else c.holiday_count end as actual_days_between from dummy_tickets t left join cte as c on t.ticket_id=c.ticket_id;
Hi Ankit, Thanks for this explaination. I was asked below question in LatentView Analytics Interview , can you share a optimal solution for that Input:- class Sub A Maths B English A Social B Chemistry A Physics B Statistics A Biology B Maths A Science B Social Result: Class Sub1 Sub2 Sub3 Sub4 Sub5 A Science Physics Social Maths Biology B English Social Maths Statistics Chemistry
You can use a similar case when statements as explained in video. Just use max or min along with case when.
bro by watching i got my first placement as data engineer...
Amazing 😍 congratulations 👏
Hello everyone i got simple solution for question number 6 . please check it select user_id from logins where cast(login_timestamp as date)>= all( select distinct cast(login_timestamp as date) as dt from logins) Thanks,
Great
Excellent explanation Ankit. Thank you. Can you please provide emp,order table datasets .so, that we can practice the same.
select a.phone_number, start_time, end_time , Timestampdiff(minute, start_time,end_time) as duration from ( select phone_number, start_time , row_number() over (partition by phone_number order by start_time asc rows between unbounded preceding and unbounded following ) s_rnk from call_start_logs_ank ) a Inner join ( select phone_number , end_time , row_number() over (partition by phone_number order by end_time asc rows between unbounded preceding and unbounded following ) e_rnk from call_end_logs_ank ) b on a.phone_number=b.phone_number and s_rnk=e_rnk