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
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♥️♥️♥️
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
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!
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
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;
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
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
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;
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
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
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
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
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
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;
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,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;
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 *, 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;
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;
