Top 10 SQL Interview Queries | Popular SQL Queries for SQL Interview

Рет қаралды 228,983

Күн бұрын

In this video, we look at 10 commonly asked SQL Queries during interviews. These are in my opinion top 10 SQL queries to learn before going to any SQL Interview. These 10 queries will cover most of the important SQL concepts hence if you can solve these 10 queries then you would have a very good understanding of SQL.
Download all 10 SQL query scripts, solutions, and problem statements from my blog below:
techtfq.com/blog/top-10-sql-i...
Check out one of the BEST platforms to learn SQL:
learnsql.com/?ref=thoufiqmoha...
Get yourself a "Certificate of Competency in SQL" for free from below:
learnsql.com/sql-skill-assess...
Timestamp:
00:00 Intro
01:03 Query 1
04:25 Query 2
07:34 LearnSQL
09:34 Get your Free SQL Competence Certificate
10:14 Query 3
12:59 Query 4
16:05 Query 5
19:56 Query 6
23:27 Query 7
26:03 Query 8
27:58 Query 9
31:13 Query 10
More Videos:
Delete Duplicate data (10 ways):
• How to remove Duplicat...
Window function Frame clause Tutorial:
• SQL Window Function | ...
JOINS Tutorial (SELF Join)
• SQL JOINS Tutorial for...
Recursive SQL Tutorial:
• Recursive SQL Queries ...
Subqueries Tutorial:
• Subquery in SQL | Corr...
Thanks for watching!
Thoufiq | techTFQ

Пікірлер: 216

@techTFQ 7 ай бұрын

Check out the learn SQL platform here: learnsql.com/?ref=thoufiqmohammed Get yourself a "Certificate of Competency in SQL" for free from below: learnsql.com/sql-skill-assessment

@Dreamtitan 7 ай бұрын

Amazing content Thanks man

@Malathi_Steps 7 ай бұрын

Thank you sooo much Toufiq for this SQL course! Thanks a lot! 🙏

@techTFQ 7 ай бұрын

Your welcome bro

@diamondsandlipgloss 9 күн бұрын

where is the link to your blog?

@Malathi_Steps 7 ай бұрын

Hi Toufiq! I have gone through SQL course from the beginning to brush up my skills and all the concepts. In the recent interview, I cleared all the technical rounds and I got placed last week with a good hike. Your videos and your teaching helped me a lot.! I am really Happy! Please keep going. God bless you!

@techTFQ 7 ай бұрын

Congragulations Malathi.. very happy for you. Super glad to know my content helped :)

@Malathi_Steps 7 ай бұрын

Thank you soo much! @@techTFQ

@sesharoxz5311 7 ай бұрын

Can you please share the course videos

@richakapoor445 7 ай бұрын

@@Malathi_Steps hi , could you please some SQL interview questions which was asked in your interview?, as my interview is tomorrow

@Malathi_Steps 7 ай бұрын

@@richakapoor445 - Sure. There are mainly these concepts were asked: 1. Finding Duplicates, deleting the duplicates(not all the records, 1 should be there), 2. all types of joins, especially the self join 3. Differences b/w delete, drop, truncate 4. Rank(),Dense Rank(), Row num 5. last 10 records, first 10 records 6. highest salary, lowest salary of an employee in each dept 7. set operators and the difference b/w union and union all 8. data base related questions like: how to test if source DB and Target DB are different 9. questions on like operators ex: name starts with letter 'M' and ends with 'N' etc. 10. all null functions. case statement etc. For me, they asked about API testing as well(their requirement). Hope this helps! All the best!

@ezhankhan1035 6 ай бұрын

Your Channel is one the best when it comes to taking SQL to the next level. Really appreciate this content, thank you!

@vijayreddy5328 6 ай бұрын

Thank you Toufiq, for question 4 we can do in very simple way instead writing such complex query select least(startingpoint,endingpoint),greatest(startingpoint,endingpoint),distance from distance;

@debapriyabehera7146 4 ай бұрын

After watching your SQL videos I thought my thinking capability increase gradually that where to think about to write a query....😊 Thanks a lot.....

@neha_7gupta846 5 ай бұрын

Thankyou , Its helpful and correct question which are asked in Interviews

@juno7893 4 ай бұрын

Very informative. Thank you🙏

@malcorub 4 ай бұрын

Awesome video!!! I'm using SQL server. What I do to follow along is CREATE TABLE and INSERT INTO the same simple tables/data and try to solve it before watching your solutions. Some we did exactly the same.... others we went about it a different way. Either way, this is a fantastic video to prep you for SQL Interviews. Thank you!!!!

@tonysun203 7 ай бұрын

Hi Toufiq. Thanks a lot for providing so many useful examples on solving SQL. I have been learning quite a lot from your videos and your SQL / python courses. Really appreciate for these courses and videos that you have done for us. Also, thanks for introducing the learnSQL website to us. I have registered for the life time eLearning also. Also, you are the best teacher I've met on learning SQL. Hope you continue to make these quality and useful video for us. Keep going brother 👍 From Tony

@techTFQ 7 ай бұрын

Thank you Tony. Glad it helped :)

@Batira583 7 ай бұрын

Always a pleasure to watch your videos Thofik. I m learning a lot from you .

@techTFQ 7 ай бұрын

Thanks a lot bro. Truly appreciate it

@user-vi2fp6dl7b 3 ай бұрын

Good job! Thank you very much!

@mmmuneer707 7 ай бұрын

Hi Toufiq!! All question are so interesting. I just found few things which effects the query. Q1) The table has only 2 duplicate rows, assume if table has 3 duplicate rows then sol 1 and sol 2 won't work as it will select MAX() and MIN() model_id. Here most accurate sol is sol 3 using ROW_NUMBER(). Q7) In this to write negative amount as (amount$), || won't work in MYSQL instead CONCAT() can be used.

@ramshataqdees5570 7 ай бұрын

partition by which column?

@ramshataqdees5570 7 ай бұрын

how can i call dataset in first query as having duplicates ?since there is a primary key id

@umangbhatnagar1415 5 ай бұрын

what the hell are you saying ?? It will work, analyse sol1 and sol2 and then think. Just don't mug up !!

@ryuhayabusa3540 5 ай бұрын

|| also knowns as pipes does work in mysql. You just have to turn it on by doing this: SET SQL_MODE = 'PIPES_AS_CONCAT';

@bugstophere 6 ай бұрын

Nice explanation and it is very useful 👌👌

@tmstechschool7239 7 ай бұрын

Dear Toufiq, I would say thank you for your effort. On Question 5 I didn't get the point of joining with the original table, I think it could work as expected with out joining the cte with the original table as follow unless we have some edge condition we should consider. with recursive cte as ( select id, item_name, total_count, 1 as level from travel_items union all select cte.id, cte.item_name, cte.total_count -1, level + 1 as level from cte where cte.total_count > 1 ) select id, item_name, level from cte

@RohitSaini52945 5 ай бұрын

with recursive cte as ( select id, item_name, total_count, 1 as lvl from travel_items union select id, item_name,total_count, lvl+1 as lvl from cte where lvl

@priyaharshitha6586 22 күн бұрын

yeah true. Even i was wondering why there is a join and have I understood recursion in a wrong way.

@inbox.sanatan 7 ай бұрын

Haven't seen any channel creating this gold level across platform

@techTFQ 7 ай бұрын

Thank you:)

@akhilsai7170 15 күн бұрын

What... referring to other videos instead of explaining briefly in a single video.

@user-so8fh2hi9f Ай бұрын

Great video! However, I think it is for more advanced SQL learners. Although i am an intermediate learner, it was very difficult to understand the concepts. But I keep learning..

@muralibaddela9201 7 ай бұрын

Excellent questions Toufiq , covers quite a bit of SQL innards

@techTFQ 7 ай бұрын

Thanks:)

@sujaa1000 6 ай бұрын

Thank you very much TFQ, this was so helpful!

@akhilsai7170 15 күн бұрын

What was actually helpful, he was just pointing out to his other videos more than 20 minutes of the video.

@mohamedaaasimoo5209 3 ай бұрын

Top 👍

@crownaradhya 7 ай бұрын

Thank you , bahut sikhne ko milta he , aur questions late raho 👌👍

@techTFQ 7 ай бұрын

Welcome

@anuragbawankar685 27 күн бұрын

Thanks you Sir!!!

@mohammedshahil4898 7 ай бұрын

Another quality content from Techtfq👌👌👏 Keep going brother🙌

@techTFQ 7 ай бұрын

Thanks:)

@ArunKumar-bp5lo 7 ай бұрын

the perfect video i never know i needed

@techTFQ 7 ай бұрын

Thank you:)

@m.s.k5300 7 ай бұрын

Nice thofiq

@techTFQ 7 ай бұрын

thanks buddy

@sevsxes 7 ай бұрын

Thank you very much for such detailed info

@techTFQ 7 ай бұрын

Welcome:)

@aumprakashdehury 7 ай бұрын

Thank you Thoufiq, Really leaned a lot from you.

@techTFQ 7 ай бұрын

Glad to hear that

@jyothikamatham98 6 ай бұрын

Thanks a lot

@mohdshajar247 6 ай бұрын

Hi Taufiq, I just solved the problem no . 5 without join. Let's have a look and lemme know if i am correct. with recursive cte as ( select id, item_name, total_count, 1 as level from item union all select cte.id, cte.item_name, total_count - 1, level+1 as level from cte where total_count > 1 ) select cte.id, cte.item_name,level as cnt from cte order by id asc

@shekhark1139 7 ай бұрын

Please make a video about data execution in sql server, how to identify bad or good ( efficient or inefficient queries) , how the cost of query in percent matters

@aleh3627 7 ай бұрын

Can you make a video explaining how the joins on different row number ids or less than row numbers id work? I can't fully understand it. Thank you for all the great info!

@WelcomeToMyLife888 7 ай бұрын

Awesome content as usual!

@techTFQ 7 ай бұрын

Appreciate it!

@rahulshahapure2298 7 ай бұрын

Superb !!.. Your content really helps with approach that could be taken. 🙂👍

@techTFQ 7 ай бұрын

Glad to hear that

@jalajareddy6397 3 ай бұрын

select max(salary) , min(salary) , dept from employee group by dept ..... i think this is a better solution for query 2 ......please let me know if any mistake from my end

@umangbhatnagar1415 7 ай бұрын

Really Really Awesome !!

@techTFQ 7 ай бұрын

Thanks a lot!

@mohammadshahbaz3287 6 ай бұрын

@techTFQ an easier solve for the issue with min function is this - select *, min(salary) over(partition by dept order by salary) as min_sal , max(salary) over(partition by dept order by salary desc) as max_sal from table; This will ensure the frame issue wont come into picture, right?

@kusumsharma7293 5 ай бұрын

Hi Toufiq! I have been following all your videos regarding sql but this is can you make more videos, on my sql work bench and interview questions also

@sushant8469 29 күн бұрын

Superb

@srinubathina7191 7 ай бұрын

Thank you Amazing content

@techTFQ 7 ай бұрын

Glad you enjoy it!

@JaskaranSingh-io8uj 5 күн бұрын

WOWWWW , BRO Exact same question have been asked to me in interview , i mean they has screen shot of your tables and they were asking .. Btw i clear the techincal round

@user-xe9zi9yw2e 7 ай бұрын

Hi, I've been working as a programmer for about two years in a financial company. However, I still struggle with understanding complex queries. While I get the few chance to write queries at work, there are instances where I need to read long queries, sometimes exceeding 500 sentences, due to unexpected circumstances. I genuinely make an effort to read and comprehend them, but when multiple challenging grammar concepts like outer joins and subqueries are used together, I find it difficult to grasp. In this situation, what should I focus on learning to improve my ability to read complex and lengthy query sentences? I really want to enhance my skills in this area.

@Aparna.Ratheesh 7 ай бұрын

Hi TFQ, Thanks for these free tutorials. Please do share a collection of medium and tricky SQL queries and solutions so we can practice before an interview.

@techTFQ 7 ай бұрын

kzfaq.info/get/bejne/l6tmZZaXy53em4E.html&pp=gAQBiAQB

@nitinmehta9738 2 ай бұрын

Excellent Thoufiq, thanks. Where can i find these queries for practice ?

@shruthigowda5985 7 ай бұрын

Pls make query tuning videos..

@fathimafarahna2633 7 ай бұрын

Always a privilege for learners to get your video … You are doing a good service 👌God bless your efforts

@techTFQ 7 ай бұрын

Thank you:)

@ramjigupta174 7 ай бұрын

Hi @techTFQ! In problem 10, Why are we not considering a case where order status is submitted and created for same user (3rd statement in the union)??

@leeellllee 5 ай бұрын

thank you for this video! would you say this level of SQL knowledge is generally tested for data engineer/data scientist roles as opposed to business functions? for example at a company like google or meta. thank you!

@vinaygc1284 3 ай бұрын

thanks fir video Do yiu have videos for plsql

@maghy_kethychannel 7 ай бұрын

hi toufiq plz do more videos on data analytics and how to do more analytical work with excel.plz plz

@techTFQ 7 ай бұрын

Noted

@zuko655 5 ай бұрын

Small improvement for Query 2 Solution: As per MySQL 8.0 Docs, the frame_clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when ORDER BY is not present. And the order of the partitioned rows does not matter for the purpose of the query. Therefore you can remove the ORDER BY clauses for both partitions: SELECT *, MAX(salary) OVER(PARTITION BY dept) AS highest_salary, MIN(salary) OVER(PARTITION BY dept) AS lowest_salary FROM employee;

@saivaibhav3331 7 ай бұрын

For question 4 we can do like this also with cte as ( select *, row_number() over(order by (select null)) as rn from src_dest_distance) select source,destination,distance from cte where rn%20

@sunidhas 7 ай бұрын

The solution in the video for question 4 doesn't work if there is only one entry like mumbai to kerala. but this solution works on that criteria.

@zizu7755 4 ай бұрын

Absolutely better solution. This query covers all scenarios and also in SQL Server you are not able to write ROW_NUMBER without ORDER BY clause, what he did in his video.

@shivinmehta7368 6 ай бұрын

i think query 2 output is wrong for IT department as we have to find highest and lowest salary for each department and the query you mentioned is showing other salaries as well my solution for q2 select * from ( select *, max(salary) over(partition by dept rows between unbounded preceding and unbounded following) as "highest_sal", min(salary) over(partition by dept rows between unbounded preceding and unbounded following) as "lowest_sal" from employee order by dept,salary desc)x where salary in(highest_sal,lowest_sal);

@v75tan 7 ай бұрын

Great Work How I can select all the row specific column data in one column separate by ,. Like in a table userdetails has a column fullname. On 'select fullname from userdetails' I should not get rows. I should get single column (string) where values are separate by , Like: Ram Sing, Shayam, Sita Jha, Gita Migan

@akhileshsoni1171 16 күн бұрын

As per my understanding, you can use List_agg function.

@bhavini9589 4 ай бұрын

Can you please provide vedio for performance tuning or SP optimization

@premanandramasamy Ай бұрын

Hi @Toufiq, Greeting for the day champ!! One doubt at problem#4. In case if only one source and destination is there in original table, expected output will not return that unique one right?

@vijaykumar-vp7mx 7 ай бұрын

Can you make videos on sql developer Road map to be a sql developer please

@DataAnalyst251 7 ай бұрын

Hi taufiq! Can you make logical interview questions more?

@techTFQ 7 ай бұрын

Can you clarify what you mean by logical questions?

@DataAnalyst251 7 ай бұрын

I have reviewed your top 25 SQL questions on the blog and also watched the video on Joins for interviews. The interviewer has been posing more questions of this nature, particularly those based on scenarios.@@techTFQ

@mohammadshahbaz3287 6 ай бұрын

Hi Toufiq, for Quest #6, part 2 each team plays every other team twice, a much simpler solution would be to do Union ALL of part 1 I.e. every team plays other team once *2 i.e. union ALL of this , right? Let me know if there's some flaw in my thinking?

@MaddyIndia Ай бұрын

For #1 , solution #1 group by on name, color and brand.

@da_vinci5100 7 ай бұрын

Hey Toufeeq! I have been trying buy your SQL recording from your website but it doesn't allow me to move forward from cart. Can you fix this?

@Uzzis-Life 2 ай бұрын

Hi Taufiq, I had been asked to write sql query to names in alternate rows, say for example a table have "name" colum having 10 rows, data as "abc" 5 rows and "xyz" 5 rows, and I need to display these "abc" and "xyz" in alternate rows as abc xyz abc xyz so on

@Development-fz5vq 2 ай бұрын

Thank you for creating this video. It was excellent. In Question 10, the 3rd union should also consider 'CREATED'. d2.status in ('DELIVERED','CREATED'))

@kumaresanigator 7 ай бұрын

alternative for Q10: SELECT CUST_NAME, CASE WHEN TOT_CNT = DELIVER_CNT THEN 'DELIVERED' WHEN DELIVER_CNT > 0 THEN 'IN PROGRESS' WHEN TOT_CNT = submit_cnt THEN 'AWAITING PROGRESS' ELSE 'AWAITING SUBMISSION' END STATUS FROM ( select cust_name, sum(case when status = 'DELIVERED' then 1 else 0 end ) as deliver_cnt, sum(case when status = 'SUBMITTED' then 1 else 0 end ) as submit_cnt, count(order_id) tot_cnt from cust_orders group by cust_name )

@vinayakyerekar762 7 ай бұрын

Please Explain query No. 6 in detail.

@moviebuff3030 3 ай бұрын

For query 5, why there's need of join first base table will work and input for the 2nd query. then why need of join in query 5. i got solution without join in MS SQL with cte as (select id, item_name, total_count from travel_items union all select id, item_name, total_count - 1 as total_count from cte where total_count > 1 ) select * from cte

@sowjyintivanta 6 ай бұрын

Hi,can u please tell me the difference between database, data warehouse, data lake and data mart . Please 🙏

@vp3198 18 күн бұрын

@techTFQ tau ji second que mein window function ki kya jarurat thi direct group by se ho jata

@jasnoorvirk1047 24 күн бұрын

sir in the 5th query you have used join with cte as (select * from travel_items union all select id,item_name,total_count-1 from cte where total_count>1 ) select id,item_name from cte order by id; this much is also sufficient could you explain the reason?

@ManojKumar-rm6io 2 ай бұрын

Thanks for such good explanation on these problems. I have a question about query5, why do we need join in the recursive part. Will it not work woithout join. e.g. below query worked for me. WITH RECURSIVE trvl_itm AS ( SELECT id, item_name, count_of, 1 AS level FROM travel_items UNION ALL SELECT id, item_name, trvl_itm.count_of-1, LEVEL+ 1 AS level FROM trvl_itm WHERE trvl_itm.count_of > 1 ) SELECT id, item_name, level FROM trvl_itm ORDER BY id, level;

@mohammadshahbaz3287 6 ай бұрын

Hi Taufiq, My simpler solution - with base as( select cust_name , count(distinct case when status='DELIVERED' then order_id end) as DELIVERED , count(distinct case when status='SUBMITTED' then order_id end) as SUBMITTED , count(distinct case when status='CREATED' then order_id end) as CREATED , count(distinct order_id) as tot_orders FROM cust_orders group by 1) select cust_name, case when tot_orders=DELIVERED then 'COMPLETED' when tot_orders>DELIVERED AND DELIVERED>0 then 'PROGRESS' when tot_orders=SUBMITTED then 'AWAITING PROGRESS' ELSE 'AWAITING SUBMISSION' END AS FLAG FROM BASE;

@udhaybhaskarbellamkonda1678 7 ай бұрын

Hi Taufiq When will you start Python Bootcamp let me know pls

@techTFQ 7 ай бұрын

Sure Ill be announcing in LinkedIn probably next month

@kumaresanigator 7 ай бұрын

Alternative Q4 solution: select distinct substr(src_desc, 1, instr(src_desc, '-', 1, 1) -1 ) as source, substr(src_desc, instr(src_desc, '-', 1, 1) + 1 ) destination, distance from ( select destination, source, distance, case when source > destination then source || '-' || destination else destination || '-' || source end src_desc from src_dest_distance )

@vijayreddy5328 6 ай бұрын

Below also easy alternatives : select least(startingpoint,endingpoint),greatest(startingpoint,endingpoint),distance from distance; OR select startingpoint, endingpoint, distance from ( select startingpoint, endingpoint, distance,row_number() over (partition by distance order by distance) as rn from distance )where rn=1;

@sudarshansbhat8665 7 ай бұрын

Hi techtfq, can you please elaborate how the join works internally when 2 or more tables are joined. I am getting confused often when I try to join the third table. Please help me in this with clear picture how the intermediate tables are created and within this already joined two tables are joined with third table. So after joining the first two tables when I write a condition to join third table from which table I should pick the column to join condition.

@akhileshsoni1171 16 күн бұрын

Hi Sudarshan, it's simple to join third table with the result of two table's join. all you have to decide is which table you want to get the result fully. If the result of first two tables need to be obtained fully then you can join third table with left join and if matching then you can perform inner join on the third table. Hope it would help.

@kshitijadeshmukh6800 7 ай бұрын

Best video, I have faced a few of these exact questions from this video in my interviews. Thank you @techTFQ

@techTFQ 7 ай бұрын

Welcome:)

@EditFolio 5 ай бұрын

I have a doubt in the 1 query 1 solution what if the color is different but as per your code it will delete that record also right?

@pingdingdongpong 5 ай бұрын

For Q4, why can't you just do where source < destination? or swap and distinct.

@pratik112ful 6 ай бұрын

Can I expect this kind of sql questions or more challenging than these with 2 years of DE experience?

@Learn_lane 7 ай бұрын

11.00

@rahulchandra759 2 ай бұрын

Can you pls share create table and data scripts ? not sure if you have already shared somewhere

@user-lh3dy8jo3d 7 ай бұрын

In the car duplicate question, you haven't mention color but if the color is different then it won't be a duplicate one right..then is it right to delete the record without considering color? Please clarify

@user-or4uw1uv1e 7 ай бұрын

I have same question. Because we consider that the rows are different if they differ in at least one column.

@guneyfatma 4 ай бұрын

in query 2, is it not possible to use group and getting min and max of the salaries? It seems easier, I could not get why you used partition.

@HughQB 7 ай бұрын

can we get the db data your using in the video?

@babhijit 2 ай бұрын

These types of sql questions are asked for data engineer/ data analyst roles only or for SDE roles also ?

@jagadishgorijala1513 6 ай бұрын

Pls let us know when you are going to start Advance SQL Advance level 2024

@Naveen_space_mama999 18 күн бұрын

Hey @tech TFQ I can understand the queries and scripts already written by someone but I'm unable to write new scriptesand unable to execute where I have to pick up what to do to overcome this drawback can you let me know

@rohandevang5530 6 ай бұрын

Easy Query... SELECT distinct least(source, destination) AS source, greatest(source, destination) AS destination, distance FROM src_dest_distance;

@michellexu2675 2 ай бұрын

for first question, can we use SELECT DISTINCT* FROM cars

@Prasanthi1984 7 ай бұрын

in your vlog employee table creation script is not working for 2019 version ,fyi

@gautham8364 5 ай бұрын

in question 3 what if the rows are not arranged car-wise, car1,car2,car3, then the lagging query would be wrong?

@srishanvithatrendingcollection 7 ай бұрын

Hi Toufiq is there any new session start for python beginners from this December. I'm from india

@techTFQ 7 ай бұрын

Probably from Jan

@prikshitbatta 3 ай бұрын

In question 2, I think there is no need for frame clause?

@mitul2312 7 ай бұрын

Query 2 can also be written as, a small change Select *, max(salary) over (partition by dept order by salary desc ) as highest_salary, min(salary) over (partition by dept order by salary) as lowest_salary from employee;

@ksofficerofficer7858 7 ай бұрын

Could you help me I'm struggling joins

@Kiba_kua 2 ай бұрын

Are these questions asked in Business Analyst position as well?

@tanmayhadke9161 7 ай бұрын

Can I know which IDE do you use for SQL?

@techTFQ 7 ай бұрын

PG Admin

@VINAYKUMAR-gv1sw 2 күн бұрын

Hi @techTFQ can you please share the solution file .?

@abhijitvernekar593 Ай бұрын

select * from src_dest_distance where source

@priyaharshitha6586 27 күн бұрын

HI Toufiq, in the second question can't we write the query without using the frame clause? can anyone help me?

@alexjenkins8026 5 ай бұрын

Q3: if days >= 10 anywhere your code will fail as it's a string and will be ordered 1, 10, 2, 3, 4. Q4: if not all source and destinations are duplicated you will remove records that only appear once. Use LEFT JOIN to ensure you're not deleting data. Q5: recursion is very slow, newer SQLs will have a work-around: SELECT id, item_name FROM travel_items CROSS JOIN LATERAL (SELECT EXPLODE(SEQUENCE(1, total_count)) AS num) ORDER BY id, num

@davestorm6718 2 ай бұрын

you need to add color to your delete queries - it won't work unless you group by ALL fields (excluding the id), otherwise, you'll delete a non-dup record

@devarapallivamsi7064 2 ай бұрын

My ans to Q4 -- Assumes the duplicates are immediate to each other. WITH dups AS ( SELECT *,CASE WHEN LEAD(destination) OVER(ORDER BY distance) = source THEN 1 ELSE 0 END AS dup_indic FROM src_dest_distance ) SELECT source,destination,distance FROM dups WHERE dup_indic = 1;

@DenverTrading 4 күн бұрын

Excellent video! #10 simpler solution ------------------------------------- ;WITH status_counts AS ( SELECT cust_name As customer_name ,SUM(CASE WHEN status = 'CREATED' THEN 1 ELSE 0 END) AS cre_count -- Must use SUM() because COUNT() counts 1s and 0s ,SUM(CASE WHEN status = 'SUBMITTED' THEN 1 ELSE 0 END) AS sub_count ,SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) AS del_count ,COUNT(status) AS num_orders FROM cust_orders GROUP BY cust_name ) SELECT customer_name ,CASE WHEN num_orders = del_count THEN 'COMPLETED' WHEN num_orders = sub_count THEN 'AWAITING PROGRESS' WHEN num_orders > del_count AND del_count > 0 THEN 'IN PROGRESS' ELSE 'AWAITING SUBMISSION' END AS final_status FROM status_counts