Solving an Amazon SQL Interview Question on Notepad

[@ankitbansal6]

Hit the like button and let me know any other questions you want me to solve on notepad . Send it to [e-mail]

[@483_saurabhjindal6]

Sir I have written query for this question. Please check my query in comments

[@PowerBiWithVivek]

Can't we us >='2021--01-01' at the end of query, will this not give more accurate result?

[@deepeshmatkati3058]

Your are at point on focusing the problem faced by candidates during an interview, also you are a master of breaking down the problem into chunks and giving us a simpler yet effective solutions!

[@nagendras9696]

Amazing video Sir. Please upload more videos in this format only, writing query first in notepad and then executing in SQL server. This will be really helpful in interview preparation

[@myjourney352]

Thank you so much Ankit, Dateadd function is not with Postgres. I've solved this using this approach. with cte as ( select *, event_date + INTERVAL '1 month' * subscription_period AS Validity_Date, row_number() over(partition by customer_id order by event_date desc) as rn from subscription_history where extract(year from event_date) = 2020 and customer_id not in (select customer_id from subscription_history where event= 'C' and extract(year from event_date) = 2020)) select customer_id,marketplace,event,event_date,Validity_Date from cte where rn=1 and Validity_Date >='2020-12-31'

[@rajat1999gupta]

Thank you for your amazing content on sql, you really are a great sql mentor

[@AmanRaj-p8w]

Mysql Solution: with cte as ( select *, row_number() over (partition by customer_id, yr order by event_date desc) as rw from ( select *, year(event_date) as yr, month(event_date) as mth from subscription_history ) as x where yr = '2020' ) select customer_id, marketplace, event_date, event, subscription_period from cte where customer_id not in (select customer_id from cte where event = 'c' ) and subscription_period + mth > 12

[@AyushAgrawal-l8q]

I faced the same problem but with an additional event status ‘E’ which stands for expiration

[@mridulraj4334]

with cte as ( select customer_id,marketplace,event_date,event,subscription_period, (CASE WHEN event_date = (max(event_date) over (partition by customer_id)) AND event !='C' THEN 1 ELSE 0 END) as flag, DATEADD(month,subscription_period,event_date) as subscription_end_date from subscription_history where event_date='2020-12-31' GROUP BY marketplace

[@AyushAgrawal-l8q]

I faced the same problem but with an additional event status ‘E’ which stands for expiration. Can you help the community by adding this additional event and solving for that as well ?

[@chitturusaianisha4115]

Thanks for the video, I have a question, what if the customer cancels on the (max date of year 2020) but still has subscription months left? in the solution we are only checking the customer's (max date of 2020) and event is not in 'C'

[@sravankumar1767]

Superb explanation Ankit 👌 👏 👍

[@manaliverma3779]

with cte as ( select customer_id, marketplace, event, event_date as event_start_date, dateadd(month, subscription_period, event_date) as event_end_date from subscription_history) select marketplace, count(distinct customer_id) as active_users from cte where event in ('S', 'R') and event_start_date between '2020-01-01' and '2020-12-31' and event_end_date between '2020-01-01' and '2020-12-31' ;

[@nikhilpurwar697]

without union with cte as( select customer_id, dateadd(month,subscription_period,event_date) as newdate from subscription_history where event in('R','S')and year(dateadd(month,subscription_period,event_date)) ='2021') select distinct customer_id from cte where customer_id not in (select customer_id from subscription_history where event='C' and year(event_date)='2020')

[@bindusharma8625]

Respected sir, can we solve this question by: select*from subscription_history where event_date

[@Sudarshan-d7s]

Bro pls bring more problems like that or just bring a series and KZbin community members pls paste all your interview experiences here so that will help all of us ❤

[@ankitbansal6]

Complex SQL Questions for Interview Preparation: kzbin.info/aero/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb

[@avi417]

with cte as ( select *, row_number () over(partition by customer_id order by event_date desc ) as rn from subscription_history where event_date = '2020-12-31';

[@Datapassenger_prashant]

As usual amazing content

[@VanshGudka]

Sir make some videos on sql projects as well ..

[@deaspirant]

Hi Ankit, I have Experience on Tableau and learning sql and python from your courses is it good to change my career from tableau developer to data engineer, is it easy to work as a data engineer if we don't have real time experience

[@vaibhavjain9094]

hello sir is there any difference between your youtube videos and namstesql course ? sir my humble suggestion please make a clear video in which tell us about the course and the difference (if any) between videos you make on youtube. because there is lot of confusion to buy namaste sql or not. if you read this comment at least please reply . thanku

[@ankitbansal6]

On KZbin I have solved multiple problems. The course covers all the concepts from basic to advanced. Along with that you have assignments and projects.

[@srinivasulum414]

Nice Explanation Ankit 👌

[@ankitbansal6]

Thanks 🙂

[@mukeshkumaryadav350]

Best part is you share the table creation script 😅

[@vinil9212]

If you want count by location: with cte as ( select *, ROW_NUMBER() OVER (Partition by customer_id order by event_date desc) as rnk from subscription_history where event_date='2020-12-31' group by marketplace;

[@483_saurabhjindal6]

select distinct(customer_id),marketp lace from subscription_history where year(event_date)=2020 and customer_id not in (select distinct customer_id from subscription_history where year(event_date)=2020 and event='C)' group by marketplace

[@nikhilpurwar697]

with cte as( select customer_id, dateadd(month,subscription_period,event_date) as newdate from subscription_history where event='R' and year(dateadd(month,subscription_period,event_date)) ='2021' union select customer_id, dateadd(month,subscription_period,event_date) as newdate from subscription_history where event='S' and year(dateadd(month,subscription_period,event_date)) ='2021') select distinct customer_id from cte where customer_id not in (select customer_id from subscription_history where event='C' and year(event_date)='2020')

[@prateek-i5w]

Do you have any plans on launching another live session of SQL course?

[@ankitbansal6]

It will take time . Go for recorded once. Later you can join live by paying the difference.

[@kedarwalavalkar6861]

with cte as ( select * from subscription_history where event_date < '2021-01-01' ) ,cte2 as ( select * from ( select * ,row_number() over(partition by customer_id order by event_date desc) as rn from cte ) x where x.rn = 1 ) select customer_id ,marketplace ,event_date ,subscription_period ,event ,event_date + INTERVAL '1 month' * subscription_period AS valid_until from cte2 where event 'C' And event_date + INTERVAL '1 month' * subscription_period >= '2020-12-31'

[@florincopaci6821]

Hello This is my solution in sql server but is too lenghty and to complicated comparing with your solution- with flo as ( select * , sum(case when year(event_date)=2020 and subscription_period is null then 0 else 1 end)over(partition by customer_id ) as grp, count(event_date)over(partition by customer_id )as cnt, max(case when year(event_date) 2021 and subscription_period is not null then event_date end )over(partition by customer_id)as last_date_not_null from subscription_history ) select customer_id from flo where grp=cnt and subscription_period is not null and event_date=last_date_not_null and dateadd(month, subscription_period, last_date_not_null)>'2020-12-31';

[@arjundev4908]

WITH CTE AS(SELECT * FROM subscription_history WHERE event_date = '2020-12-31';

[@AkhilManhas-vi1qc]

We already filter date in cte . Then why we use date add

[@AkhilManhas-vi1qc]

Samaj gaya

[@piyushsingh6084]

Hello Ankit, I have purchased the SQL course, but unfortunately the website is not working , Please do the needful I hope it gets resolved asap

[@ankitbansal6]

Checking

[@ankitbansal6]

It's back

[@piyushsingh6084]

@@ankitbansal6 thanks sir, also the telegram link provided in the Hindi version of SQL ,recorded lectures is not clickable, idk if it's active or not , please check that also if it's active, if it's not what alternative solution I hv to raise doubts if any :)

[@rajkumarpanigrahi2013]

Brother please try to upload video regular

[@ankitbansal6]

Sure

[@coding6763]

new course when ?

[@SANDEEPSHARMA-xr1ke]

Sir need help for this problem

[@sagarsonawane9007]

Save Hindus in Bangladesh

[@ankitbansal6]

We are with you. Let us know how we can help.

[@bharathms5560]

Okay bro we understand your point but why are you commenting here? It's not the place for that 😥

[@Mju98]

Have some ethics @sagarsonawane9007. I can pray for u if u are bangladeshi hindu. But this is not the right platform. Twitter is there you can contact the officials

[@rafidrabbi]

Bangladesh hindus are saved....Dont worry

[@rafidrabbi]

Bangladesh hindus are saved....Dont worry