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!
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'
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
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';
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'
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 ?
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
Bro pls bring more problems like that or just bring a series and UA-cam community members pls paste all your interview experiences here so that will help all of us ❤
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')
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
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' ;
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
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')
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
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;
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
On UA-cam I have solved multiple problems. The course covers all the concepts from basic to advanced. Along with that you have assignments and projects.
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'
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';
@@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 :)
select * from (select customer_id,marketplace, sum(case when event = 'C' then 1 else 0 end )as cancelled ,sum(case when event != 'C' and date_add(event_date,interval subscription_period month) > '2020-12-31' then 1 else 0 end) as after_tnr from `seraphic-amp-433610-p2.new_dataset.subscription_history` where event_date < '2020-12-31' group by 1,2) where cancelled = 0 and after_tnr >0
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
Hit the like button and let me know any other questions you want me to solve on notepad . Send it to sql.namaste@gmail.com
Sir I have written query for this question.
Please check my query in comments
Can't we us >='2021--01-01' at the end of query, will this not give more accurate result?
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!
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'
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
Thank you for your amazing content on sql, you really are a great sql mentor
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';
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'
As usual amazing content
Superb explanation Ankit 👌 👏 👍
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 ?
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
Bro pls bring more problems like that or just bring a series and UA-cam community members pls paste all your interview experiences here so that will help all of us ❤
Complex SQL Questions for Interview Preparation: ua-cam.com/play/PLBTZqjSKn0IeKBQDjLmzisazhqQy4iGkb.html
Best part is you share the table creation script 😅
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')
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
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' ;
I faced the same problem but with an additional event status ‘E’ which stands for expiration
Sir make some videos on sql projects as well ..
Nice Explanation Ankit 👌
Thanks 🙂
Do you have any plans on launching another live session of SQL course?
It will take time . Go for recorded once. Later you can join live by paying the difference.
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
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')
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
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;
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
On UA-cam I have solved multiple problems. The course covers all the concepts from basic to advanced. Along with that you have assignments and projects.
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'
Respected sir, can we solve this question by: select*from subscription_history where event_date
I didn't understand use of row no function and having rank here. If somebody can explain Please do
new course when ?
We already filter date in cte . Then why we use date add
Samaj gaya
Sir need help for this problem
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';
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
Checking
It's back
@@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 :)
Brother please try to upload video regular
Sure
WITH CTE AS(SELECT * FROM subscription_history
WHERE event_date = '2020-12-31';
select * from
(select customer_id,marketplace,
sum(case when event = 'C' then 1 else 0 end )as cancelled
,sum(case when event != 'C' and date_add(event_date,interval subscription_period month) > '2020-12-31' then 1
else 0 end)
as after_tnr from `seraphic-amp-433610-p2.new_dataset.subscription_history`
where event_date < '2020-12-31'
group by 1,2)
where cancelled = 0 and after_tnr >0
Save Hindus in Bangladesh
We are with you. Let us know how we can help.
Okay bro we understand your point but why are you commenting here? It's not the place for that 😥
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
Bangladesh hindus are saved....Dont worry
Bangladesh hindus are saved....Dont worry