Amazing.. I had another approach. select parent_id , case when (sum(case when status = 'Active' then 1 else 0 end)) = 0 then 'Inactive' else 'Active' end as status from parent_child_status group by parent_id
Thanks for exercises 😍 And below is one of my solutions↓ select distinct parent_id, case when sum(case when status = 'Active' then 1 else 0 end) over(partition by parent_id) = 0 then 'InActive' else 'Active' end as status from parent_child_status;
May I know why you used the aggregate function in a case statement and how it actually works in a case statement? I have been trying to understand this but I couldn't 😢. Hope you will be helpful 😊
@@naveenkalyan4700 I`m happy to help) There are 2 case statements. First one I put in analytic function "SUM(1st case statement) OVER(partition by parent_id)" and then comes 2nd case statement which include that sum analytic function. If you need more detailed explanation then let me know )
@@Mrlegacy1_ Glad that you like it ) In comments I saw "CTE" solution of what I did. You can see it below↓ with cte as( select parent_id, sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag from parent_child_status group by parent_id) select parent_id, case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status from cte; I felt curious about which of this 2 will be faster in performance. Did 4 cross joins and COST difference was 234, CTE was faster. FYI: To look for COST (for Oracle sql) you can highlight query and press F10 or highlight query→right click→Explain...→Explain plan
Thanks a ton for your SQL videos 👏👏 . Your way of presenting the SQL contents is on another level. No bakwas, detailed explanation👏 I am here with another solution for the same. with c1 as (select distinct p_id,sum(case when status='Active' then 1 else 0 end) as flag from dup group by 1 ) select distinct p_id,case when flag=0 then 'InActive' else 'Active' end as status from c1
What more can I say...your videos are just self explanatory, I love this so much....I just finished solving the 22 SQL problems you posted earlier on the painters dataset, it just feels amazing. I'm always waiting for your videos to drop, I do learn a lot from them♥️♥️♥️
my solution: with cte as ( SELECT *, CASE WHEN status = 'Active' THEN 1 ELSE 0 END AS flag FROM parent_child_status ), cte2 as ( select *,sum(flag) over(partition by parent_id) as check_sum FROM cte ) select distinct(parent_id),status from cte2 where check_sum = 0 union select distinct(parent_id),status from cte2 where check_sum 0 and status !='InActive'
SOLUTION 7 : with cte as( select parent_id, sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag from parent_child_status group by parent_id) select parent_id, case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status from cte
I have also Tried in following way - With Cte_parent_child_status (Parent_Id, Sts) as ( Select Parent_Id, LISTAGG(Status, ',') Within Group(Order By Parent_Id) Sts From parent_child_status Group By Parent_Id ) Select Parent_Id, Case when REGEXP_INSTR(REPLACE(STS,'InActive','No'), 'Active')>0 then 'Active' Else 'InActive' End Status from Cte_parent_child_status Order By Parent_Id;
Hi Toufiq, i m so glad to find ur channel. Ur way of explaining things is soooooo good. Wish I had found ur channel long before. Never stop doing what u r doing!
Dear duty time 20:00 to 8:00 tak hai or check in out k table me 2 column hy empid or checktime or expected output Date-timein-timeout-late-early-dutyhour Is tarha chahiye Slove problem
with cte as( select *,sum(case when status='Active' then 1 else 0 end )over(partition by parent_id)as flag from parent_child_status) select distinct parent_Id,case when flag>0 then 'Active' else ' inactive' end as status from cte;
Same output thanks with cte as( select *, sum( case when status='Active' then 1 else 0 end )over(partition by parent_id)as flag from parent_child_status) select distinct parent_Id,case when flag =0 then 'InActive' else 'Active' end as status from cte Order by parent_id;
select parent_id, case when sum(case status when 'Active' then 1 else 0 end) >=1 then 'Active' else 'Inactive' end status from parent_child_status group by parent_id
Your videos are truly incredible, and they have proven to be exceptionally helpful in enhancing my understanding of SQL concepts. I appreciate the effort and clarity you bring to your content, making it easier for me to grasp complex ideas. Thank you for providing such valuable resources!✨✨
Hi sir I saw your video learn how to write SQL queries it's very useful video for all MySQL developers thank you so much and as parllel which course is required for MySQL
Hello sir, Can you please provide the query for the below question to fetch the information: 1.Find all the databases we have in SF non prod account. 2.Find how many tables we have in each databases 3.find total record count in each tables in each databases 4.find last access date by anyone of every table in each databases for all of the above steps, consider only those table which is active or in use 5.find each table size in GB in every databases 6. how to find the storage cost in sf for any Please help me with this
with cte as (select parent_id,max(case when status='Active' then status else null end) as Active, max(case when status='Inactive' then status else null end) as Inactive from parent_child_status group by parent_id) select parent_id,active from cte where Active is not null union all select parent_id,inactive from cte where Active is null order by parent_id
with cte as( select parent_id, COUNT(CASE when status="Active" THEN 1 ELSE NULL END) as "active_count" FROM parent_child_status GROUP BY parent_id ) select parent_id, CASE when active_count>0 THEN "active" ELSE "inactive" END AS "status" FROM cte;
select *from parent_child_status; with cte as ( select *, rank() over(partition by parent_id order by cnt desc)as rnk from( select parent_id,status,sum(marks)as cnt from( select *, (case when status = 'Active' then 1 else 0 end)as marks from parent_child_status)as x group by parent_id,status order by parent_id)) select parent_id,status from cte where rnk=1;
With CTE as (Select Parent_id,group_concat(Child_id), Count(Case when status="Active" then 1 end) as S_Count from Parent_child_Status group by Parent_id) Select Parent_id, Case When S_count>=1 then "Active" else "Inactive" end as Status from CTE;
I want to work as data analyst before I start my masters in data science and machine learning. Problem is : I have graduated recently from biomedical Science
Can you share the important SQL must do questions from platforms like leetcode, stratascratch, data lemur etc in one video to watch before every interview
with cte as( select parent_id, COUNT(CASE when status="Active" THEN 1 ELSE NULL END) as x1, COUNT(CASE when status="InActive" THEN 1 ELSE NULL END) as x2 FROM parent_child_status GROUP BY parent_id ) select parent_id, CASE when x1>0 THEN 'Active' ELSE 'InActive' END AS status FROM cte;
WITH get_active_inactive_status AS( SELECT parent_id, SUM(CASE WHEN status='Active' THEN 1 ELSE 0 END) AS active_status, SUM(CASE WHEN status='InActive' THEN 1 ELSE 0 END) AS inactive_status FROM parent_child_status GROUP BY parent_id) SELECT parent_id, CASE WHEN active_status > 0 THEN 'Active' ELSE 'InActive' END AS final_status FROM get_active_inactive_status;
WITH CTE AS(SELECT *, CASE WHEN PARENT_ID IN(SELECT PARENT_ID FROM PARENT_CHILD_STATUS WHERE STATUS = "ACTIVE") THEN "ACTIVE" ELSE "INACTIVE" END AS NEW_STATUS FROM PARENT_CHILD_STATUS) SELECT PARENT_ID, NEW_STATUS AS STATUS FROM CTE GROUP BY 1;
Min doesn’t work that way, when we do min(status) on a string it will give the lowest character string value. As active starts with A, it is the lowest string value. It will still return active in your case
Now I can watch your videos on loop all the time,
thanks thoufiq, you can make anyone fall in love with SQL.
Amazing.. I had another approach.
select parent_id
, case when (sum(case when status = 'Active' then 1 else 0 end)) = 0 then 'Inactive' else 'Active' end as status
from parent_child_status
group by parent_id
Thanks for exercises 😍
And below is one of my solutions↓
select
distinct parent_id,
case
when sum(case when status = 'Active' then 1 else 0 end) over(partition by parent_id) = 0 then 'InActive'
else 'Active'
end as status
from
parent_child_status;
May I know why you used the aggregate function in a case statement and how it actually works in a case statement?
I have been trying to understand this but I couldn't 😢. Hope you will be helpful 😊
@@naveenkalyan4700 I`m happy to help)
There are 2 case statements. First one I put in analytic function "SUM(1st case statement) OVER(partition by parent_id)" and then comes 2nd case statement which include that sum analytic function.
If you need more detailed explanation then let me know )
Your solution is tricky but simple when you do understand it, I like it👏
@@Mrlegacy1_ Glad that you like it )
In comments I saw "CTE" solution of what I did. You can see it below↓
with cte as(
select parent_id,
sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag
from parent_child_status
group by parent_id)
select parent_id,
case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status
from cte;
I felt curious about which of this 2 will be faster in performance. Did 4 cross joins and COST difference was 234, CTE was faster.
FYI: To look for COST (for Oracle sql) you can highlight query and press F10 or highlight query→right click→Explain...→Explain plan
Thanks a ton for your SQL videos 👏👏 . Your way of presenting the SQL contents is on another level. No bakwas, detailed explanation👏
I am here with another solution for the same.
with c1 as
(select distinct p_id,sum(case when status='Active' then 1 else 0 end) as flag
from dup group by 1
) select distinct p_id,case when flag=0 then 'InActive' else 'Active' end as status
from c1
What more can I say...your videos are just self explanatory, I love this so much....I just finished solving the 22 SQL problems you posted earlier on the painters dataset, it just feels amazing. I'm always waiting for your videos to drop, I do learn a lot from them♥️♥️♥️
my solution:
with cte as
(
SELECT *, CASE WHEN status = 'Active' THEN 1 ELSE 0
END AS flag
FROM parent_child_status
),
cte2 as
(
select *,sum(flag) over(partition by parent_id) as check_sum
FROM cte
)
select distinct(parent_id),status from cte2
where check_sum = 0
union
select distinct(parent_id),status from cte2
where check_sum 0 and status !='InActive'
SOLUTION 7 :
with cte as(
select parent_id,
sum(case when status = 'Active' then 1 else 0 end) as act_unact_flag
from parent_child_status
group by parent_id)
select parent_id,
case when act_unact_flag > 0 then 'Active' else 'Inactive' end as status
from cte
I have also Tried in following way -
With Cte_parent_child_status (Parent_Id, Sts) as
(
Select Parent_Id, LISTAGG(Status, ',') Within Group(Order By Parent_Id) Sts
From parent_child_status
Group By Parent_Id
) Select Parent_Id,
Case when REGEXP_INSTR(REPLACE(STS,'InActive','No'), 'Active')>0 then 'Active' Else 'InActive' End Status
from Cte_parent_child_status
Order By Parent_Id;
Thank you Tfq. I am really happy that you have made a video of my question which I have sent over an email to you.
Hi Toufiq, i m so glad to find ur channel. Ur way of explaining things is soooooo good. Wish I had found ur channel long before. Never stop doing what u r doing!
Thank you for the amazing explanation with multiple solutions. Always loved to watch your videos.
Just wow!!
Each solution was different from the other 💯
Thankyou sir 🙏
Dear duty time 20:00 to 8:00 tak hai or check in out k table me 2 column hy empid or checktime or expected output
Date-timein-timeout-late-early-dutyhour
Is tarha chahiye
Slove problem
with cte as(
select *,sum(case when status='Active' then 1 else 0 end )over(partition by parent_id)as flag
from parent_child_status)
select distinct parent_Id,case when flag>0 then 'Active' else '
inactive' end as status
from cte;
Same output thanks with cte as(
select *,
sum(
case when status='Active'
then 1 else 0 end )over(partition by parent_id)as flag
from parent_child_status)
select distinct parent_Id,case when flag =0 then 'InActive' else 'Active' end as status
from cte
Order by parent_id;
select parent_id, case when sum(case status when 'Active' then 1 else 0 end) >=1 then 'Active' else 'Inactive' end status
from parent_child_status
group by parent_id
would use QUALIFY if your database support it
Your explanations are just wow 🤟
Hello, what should be the front end tool for developing forms? How to develop reports? Are you covering them in your courses?
Really appreciate your preparation and efforts to make the videos straight to the point and easily understandable. Keep doing the great work Thoufiq.
I really learned a lot from this video!
You videos are a gem... Very informative and well explained.. Please make some videos on SQL Date format and SQL String Functions.
Your videos are truly incredible, and they have proven to be exceptionally helpful in enhancing my understanding of SQL concepts. I appreciate the effort and clarity you bring to your content, making it easier for me to grasp complex ideas. Thank you for providing such valuable resources!✨✨
Hi sir I saw your video learn how to write SQL queries it's very useful video for all MySQL developers thank you so much and as parllel which course is required for MySQL
In solution 1, why did you need to use group by in active but not in inactive?
Hello sir,
Can you please provide the query for the below question to fetch the information:
1.Find all the databases we have in SF non prod account.
2.Find how many tables we have in each databases
3.find total record count in each tables in each databases
4.find last access date by anyone of every table in each databases for all of the above steps, consider only those table which is active or in use
5.find each table size in GB in every databases
6. how to find the storage cost in sf for any
Please help me with this
Wonderfully explained, thank you!
Please explore KQL also and start making similar tutorials if possible, it's very powerful too.
Please make a video on date function. And create a table with time column and show how to insert the time
select parent_id,min(status) as status from table group by parent_id ;
Really THANK YOU SOOOO... MUCH🙂🙂🙂
you made my day, I learnt it.
Thank you so much for the easy explanation, your videos are amazing.
with cte as (select parent_id,max(case when status='Active' then status else null end) as Active,
max(case when status='Inactive' then status else null end) as Inactive
from parent_child_status group by parent_id)
select parent_id,active from cte where Active is not null
union all
select parent_id,inactive from cte where Active is null order by parent_id
Need more this type of video
with cte as(
select parent_id,
COUNT(CASE when status="Active" THEN 1 ELSE NULL END) as "active_count"
FROM parent_child_status GROUP BY parent_id
)
select parent_id,
CASE
when active_count>0 THEN "active"
ELSE "inactive"
END AS "status"
FROM cte;
select *from parent_child_status;
with cte as
(
select *,
rank() over(partition by parent_id order by cnt desc)as rnk
from(
select parent_id,status,sum(marks)as cnt
from(
select *,
(case when status = 'Active' then 1
else 0 end)as marks
from parent_child_status)as x
group by parent_id,status
order by parent_id))
select parent_id,status
from cte
where rnk=1;
Please do this type of video. thansk
With CTE as
(Select Parent_id,group_concat(Child_id),
Count(Case when status="Active" then 1 end) as S_Count from Parent_child_Status
group by Parent_id)
Select Parent_id,
Case When S_count>=1 then "Active"
else "Inactive" end as Status from CTE;
I want to work as data analyst before I start my masters in data science and machine learning.
Problem is : I have graduated recently from biomedical Science
Can you share the important SQL must do questions from platforms like leetcode, stratascratch, data lemur etc in one video to watch before every interview
I want to practise SQL, can you suggest some sites where I can practise SQL (not for Data Science purpose)?
sir, as a beginner i have a doubt...how do we connect postgre sql server to microsoft excel
Thanks 👍 explanation is really amazing ❤
Amazing.... thanks!
Thank you 😊
Amazing..
good!
Please help me solve this..write a query to get the least accounts that had the least amount sold in the month of December 2015.. thanks
with cte as(
select parent_id,
COUNT(CASE when status="Active" THEN 1 ELSE NULL END) as x1,
COUNT(CASE when status="InActive" THEN 1 ELSE NULL END) as x2
FROM parent_child_status GROUP BY parent_id
)
select parent_id,
CASE
when x1>0 THEN 'Active'
ELSE 'InActive'
END AS status
FROM cte;
Hello sir I have one doubt,in there are are 2nd solution has where rn=1,what is the meaning rn
when will start new sql batch ?
SQL king
I like it
How can you apply min or max to a string type column ? I am referring to status column
But you saw that it worked right?
Done
Hi TFQ
WITH get_active_inactive_status AS(
SELECT
parent_id,
SUM(CASE WHEN status='Active' THEN 1 ELSE 0 END) AS active_status,
SUM(CASE WHEN status='InActive' THEN 1 ELSE 0 END) AS inactive_status
FROM parent_child_status
GROUP BY parent_id)
SELECT
parent_id,
CASE WHEN active_status > 0 THEN 'Active' ELSE 'InActive' END AS final_status
FROM get_active_inactive_status;
WITH CTE AS(SELECT *,
CASE WHEN PARENT_ID IN(SELECT PARENT_ID FROM PARENT_CHILD_STATUS
WHERE STATUS = "ACTIVE") THEN "ACTIVE" ELSE "INACTIVE" END AS NEW_STATUS
FROM PARENT_CHILD_STATUS)
SELECT PARENT_ID, NEW_STATUS AS STATUS
FROM CTE
GROUP BY 1;
❤❤🔥 💯
😅i have a doubt to your last solution min(status) . what if the table has 1 inactive and 2 active then the min(status) will be inactive .
Min doesn’t work that way, when we do min(status) on a string it will give the lowest character string value. As active starts with A, it is the lowest string value. It will still return active in your case
Can anyone let me know in which editor code was running?
His is using pgadmin with dark theme.
@@Gauravop101 ok, thanks for replying
Python new batch start date plz
We can use here foreign key