- Видео 109
- Просмотров 1 676 815
ItJunction4all
Индия
Добавлен 11 авг 2016
My name is Sunil Kumar. I am a Software Engineer. I have created RUclips channel ITJunction4All to share my knowledge. I will be posting videos on SQL, Python and Excel tutorials and interview questions & answers. I am hoping that whoever follows my channel will be benefiting from it. Lastly, if you are new to my channel, please subscribe my channel for all future video updates. I can assure that my channel will impart good knowledge to all my viewers.
Thanks
Thanks
SQL Interview Questions Part 67 | Year on Year Growth Rate SQL Problem 🔥🔥
SQL Interview Questions Part 67 | Year on Year Growth Rate SQL Problem 🔥🔥
Problem Statement :- There is a table called User_Transaction which contains information about XYZ company users transactions for different products.
Write a query to calculate the Year-on-Year growth rate for the total spend of each product
YoY growth = ((current period value - last period value) / last period value) x 100
-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
itjunction4all
*Twitter:
sunilkr5672
-------------------------------------------------------...
Problem Statement :- There is a table called User_Transaction which contains information about XYZ company users transactions for different products.
Write a query to calculate the Year-on-Year growth rate for the total spend of each product
YoY growth = ((current period value - last period value) / last period value) x 100
-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :
itjunction4all
*Twitter:
sunilkr5672
-------------------------------------------------------...
Просмотров: 686
Видео
SQL Interview Questions Part 66 | Tricky SQL Interview Question & Answer 🔥🔥
Просмотров 7022 месяца назад
SQL Interview Questions Part 66 | TRICKY SQL Interview Question & Answer 🔥🔥 Problem Statement :- The relationship between the LIFT and LIFT_PASSENGERS Table is such that multiple passengers can attempt to enter the same lift, but the total weight of the passengers in a lift cannot exceed the lift's capacity. Write an SQL query that produces a comma separated list of passengers who can be accomm...
SQL Interview Questions Part 65 | PWC Interview Question & Answer 🔥🔥
Просмотров 1,3 тыс.5 месяцев назад
SQL Interview Questions Part 65 | PWC Interview Question & Answer 🔥🔥 This question has been asked in PWC interview. This question will be helpful in cracking any SQL interview Problem Statement : There are two tables namely Src and Tgt. Both table have two columns i.e. id and Name. Write an SQL query to get the expected output as below. It should display Id column and derived ‘Comment’ column w...
SQL Query Multiple Choice Question with Answer | SQL Quiz Part 3
Просмотров 3505 месяцев назад
SQL Query based Multiple Choice Question with Answer | SQL Quiz Part 3 | SQL Quiz based on GROUP BY and HAVING concepts Follow me on Social Networking Sites : *Twitter: sunilkr5672 *Instagram : itjunction4all Table and Insert Scripts for Practice Create Table EmpData ( NAME varchar(10), DEPT varchar(10), SALARY int ) Insert into EmpData Values('Tom','Finance',8000) In...
SQL Query Multiple Choice Question with Answer | SQL Quiz Part 2
Просмотров 3235 месяцев назад
SQL Query based Multiple Choice Question with Answer | SQL Quiz Part SQL Quiz based on JOIN concepts Follow me on Social Networking Sites : *Twitter: sunilkr5672 *Instagram : itjunction4all Table and Insert Scripts for Practice Create Table X( Col varchar(5) ) Create Table Y( Col varchar(5) ) Create Table Z( Col varchar(5) ) Insert into X Values(null) Insert into X Va...
SQL Interview Questions And Answers Part 64 | SQL questions for Product based companies
Просмотров 1,5 тыс.6 месяцев назад
SQL Interview Questions And Answers Part 64 Problem Statement :- There are two tables namely Job_Positions and Job_Employees Write an SQL query to get the expected output as shown in next slide. Basically it should display not filled posts as ‘Vacant’ along with the details of the employee:- 𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥 *Instagram : itjunction4all *Twitter: sunilkr5672 T...
SQL Interview Questions And Answers Part 63 | SQL questions for Product based companies
Просмотров 1,5 тыс.8 месяцев назад
SQL Interview Questions And Answers Part 63 Problem Statement :- Staff Table has two columns namely Id and Name. Write an SQL query to get the expected output :- 𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥 *Instagram : itjunction4all *Twitter: sunilkr5672 Table and Insert SQL Script : create table Staff_Tbl ( Id int, Name varchar(40) ); insert into Staff_Tbl values (1, 'Staff1'); inser...
SQL Interview Questions And Answers Part 62 | SQL questions for Product based companies
Просмотров 2,4 тыс.Год назад
SQL Interview Questions And Answers Part 62 | SQL questions for Product based companies
SQL Interview Questions And Answers Part 61 | SQL questions for Product based companies
Просмотров 2,6 тыс.Год назад
SQL Interview Questions And Answers Part 61 | SQL questions for Product based companies
SQL Interview Questions And Answers Part 60 | How to fill NULL values with the last Non NULL value
Просмотров 5 тыс.Год назад
SQL Interview Questions And Answers Part 60 | How to fill NULL values with the last Non NULL value
What is the order of execution of query in SQL? | SQL query Execution Order
Просмотров 2,2 тыс.Год назад
What is the order of execution of query in SQL? | SQL query Execution Order
How to Sort the result in alternate order of Gender in SQL | SQL query
Просмотров 3,2 тыс.Год назад
How to Sort the result in alternate order of Gender in SQL | SQL query
SQL Interview Questions & Answer - Part 59 | Google SQL Question
Просмотров 2,2 тыс.Год назад
SQL Interview Questions & Answer - Part 59 | Google SQL Question
SQL Interview Questions Part 58 | WALMART Interview Question & Answer Part - 3 🔥🔥
Просмотров 2,3 тыс.Год назад
SQL Interview Questions Part 58 | WALMART Interview Question & Answer Part - 3 🔥🔥
SQL Interview Questions Part 57 | Amazon SQL Question Part-11 | SQL Data Engineer Interview 🔥🔥
Просмотров 2,3 тыс.Год назад
SQL Interview Questions Part 57 | Amazon SQL Question Part-11 | SQL Data Engineer Interview 🔥🔥
Python Interview Question & Answer Part 4 | Two Sum problem in Python
Просмотров 712Год назад
Python Interview Question & Answer Part 4 | Two Sum problem in Python
SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time
Просмотров 1,9 тыс.Год назад
SQL Interview Questions & Answer - Part 56 | Google SQL Question | Consulting Bench Time
Difference between COUNT (*) , COUNT(1), COUNT(Col_Name) and COUNT(Distinct(Col_Name) in SQL
Просмотров 4,2 тыс.2 года назад
Difference between COUNT (*) , COUNT(1), COUNT(Col_Name) and COUNT(Distinct(Col_Name) in SQL
SQL Interview Questions Part 55 | FACEBOOK/META Interview Question & Answer | Active User Retention
Просмотров 2,8 тыс.2 года назад
SQL Interview Questions Part 55 | FACEBOOK/META Interview Question & Answer | Active User Retention
SQL Interview Questions Part 54 | WALMART Interview Question & Answer | Frequently Purchased Pairs
Просмотров 7 тыс.2 года назад
SQL Interview Questions Part 54 | WALMART Interview Question & Answer | Frequently Purchased Pairs
Python Interview Question & Answer Part 3 | First Non-Repeating Character in a String - Python
Просмотров 1 тыс.2 года назад
Python Interview Question & Answer Part 3 | First Non-Repeating Character in a String - Python
Python Interview Question & Answer Part 2 | Display duplicates in Python List
Просмотров 6462 года назад
Python Interview Question & Answer Part 2 | Display duplicates in Python List
SQL Interview Questions Part 53 | Google SQL Question | SQL Data Engineer Interview | Activity Rank
Просмотров 2,3 тыс.2 года назад
SQL Interview Questions Part 53 | Google SQL Question | SQL Data Engineer Interview | Activity Rank
SQL Interview Questions Part 52 | Amazon SQL Question Part-10 | SQL Data Engineer Interview 🔥🔥
Просмотров 3 тыс.2 года назад
SQL Interview Questions Part 52 | Amazon SQL Question Part-10 | SQL Data Engineer Interview 🔥🔥
Python Interview Question & Answer Part 1
Просмотров 1,1 тыс.2 года назад
Python Interview Question & Answer Part 1
SQL Interview Questions And Answers Part 51 | FACEBOOK / META SQL Interview Question And Answer
Просмотров 3,1 тыс.2 года назад
SQL Interview Questions And Answers Part 51 | FACEBOOK / META SQL Interview Question And Answer
SQL Interview Questions And Answers Part 50 | WALMART SQL Interview Question And Answer
Просмотров 4,4 тыс.2 года назад
SQL Interview Questions And Answers Part 50 | WALMART SQL Interview Question And Answer
SQL Interview Questions Part 49 | Amazon SQL Question Part-9 | Number of Calls between two Persons
Просмотров 2,6 тыс.2 года назад
SQL Interview Questions Part 49 | Amazon SQL Question Part-9 | Number of Calls between two Persons
How to Sort by keeping one value always at top and others in ascending order in SQL ? | SQL Query
Просмотров 2,6 тыс.2 года назад
How to Sort by keeping one value always at top and others in ascending order in SQL ? | SQL Query
Here is the my approach in mysql : with cte as (select *,lag(sequence+1,1,sequence) over(partition by grp ) as prev_sequence, sequence-lag(sequence+1,1,sequence) over(partition by grp) as grp_flag from emp_sq),cte_2 as (select *,sum(grp_flag) over(partition by grp rows between unbounded preceding and current row) as groupp from cte) select Grp,min(sequence) as min_seq,max(sequence) as max_seq from cte_2 group by Grp,groupp;
with cte_temp as( select *, lag(amount,1,0)over(partition by brand order by years) as prev from brands), cte_temp2 as( select *,case when amount>prev then 0 else 1 end as flag from cte_temp ), cte_answer as( select brand from cte_temp2 group by brand having sum(flag)=0 ) select years, brand, amount from cte_temp2 where brand in ( select * from cte_answer)
with cte as( select start_range,end_range from #sampletable union all select start_range+1,end_range from cte where start_range+1<=end_range) select start_range as id from cte order by start_range
with cte as (select *,lead(id) over(order by id) as ld from sample_table union all select id+1,ld from cte where id<ld) select * from(select id from cte group by id )subq except select * from sample_table
with cte as (select product_id,spend,years, lag(spend) over(partition by product_id order by years) as last_year_spend from(select product_id,sum(spend) as spend,year(transaction_date) as years from user_transaction group by product_id,year(transaction_date) ) as sbq) select *,round(coalesce(((spend-last_year_spend)/last_year_spend)*100,0),2) as YoY_growth from cte ---to replace null values also
Hi sir I have 1 doubt the join you mentioned like a.custid=b. custid it means for cte you have done group by and also max value you have taken then we have seen 1001 and 1002 you have joined with normally I am not understanding that in inner join should be 1 *4 it would be 4 times like 1001 1001 for both 1001 and 1002 but it is showing normal count and you please elaborate and add the comment
Great question.. even though by looking at it initially, I understood this will be done by Recursive CTE , but it took me some time to set this logic up
Thank you ! With continuous practice, you will be able to build logic faster.
What about the window functions?
Window functions operated on the result set after the HAVING clause but before the SELECT and ORDER BY clause.
@@ItJunction4all how can it run before the select clause as it can use already selected elements?
Select distinct UserId from ( Select UserId, Case WHEN DATEDIFF(day,LAG(CreatedAt) over(partition by UserId order by CreatedAt), CreatedAt) <=7 THEN 1 ELSE 0 END as Ind from Transactions_Amazon ) sub where Ind = 1
30sec straight forward to the point , thank you so much
I am glad that you are liking my videos ❤️
As always, great question from Sunil. My approach without using the inbuilt percentile_cont function:- Declare @total int; Set @total = (Select sum(num_users) from search_frequency) ; with cte_rec_median as ( Select searches, num_users, 1 as step from search_frequency union all select searches, num_users, 1+step from cte_rec_median where step < num_users ) Select CASE WHEN @total%2 = 0 THEN CAST(sum(CAST(searches as Decimal(38,1))/2) as Decimal(38,1)) ELSE sum(searches) END as median from ( Select searches, CASE WHEN @total%2 = 0 THEN CASE WHEN rn = @total/2 or rn = @total/2+1 THEN 1 ELSE 0 END ELSE CASE WHEN rn = @total/2+1 THEN 1 ELSE 0 END END as Indicator from ( Select searches, num_users, step, row_number() over(order by searches, num_users) as rn from cte_rec_median )sub )sub2 where Indicator = 1
thanks a lot for clear understanding sir
You are welcome. I am glad that my videos are helping you 😊
Awesome ❤🎉🎉
Thank you! Cheers!
Sir please teach Vlookup and hlookup
Sure Vibhan...I will make a video on Vlookup and hlookup
My Approach: with cte as( select Device_id, count(*) no_of_signals from Device group by Device_id ), cte2 as( select Locations, Device_id, count(*) cnt, count(locations) over (partition by Device_id) no_of_locations from Device group by Device_id, Locations ) select c.Device_id, c2.no_of_locations, c2.Locations as max_signal_location, c.no_of_signals from cte c inner join cte2 c2 on c.Device_id=c2.Device_id where cnt in (3,4)
Here's my solution without using LEAD or LAG function. ; with cte as ( Select S1.id as S1Id, S1.Visit_date as S1V, S1.No_of_people as S1P, S2.id as S2Id, S2.Visit_date as S2V, S2.No_of_people as S2P, S3.id as S3id, S3.Visit_date as S3V, S3.No_of_people as S3P from Stadium S1, Stadium S2, Stadium S3 where S1.id+1 = S2.id and S2.id+1 = s3.id and S1.No_of_people>=100 and S2.No_of_people>=100 and S3.No_of_people>=100 ) Select distinct * from ( Select S1id as id, S1V as Visit_date, S1P as No_of_people from cte UNION ALL Select S2id, S2V, S2P from cte UNION ALL Select S3id, S3V, S3P from cte ) sub
with cte as( select * ,case when Amount > lag(Amount, 1, 0) over(Partition by brand order by Years) then 1 else 0 end flag from brands ) select Years, Brand, Amount from ( select *, sum(flag) over(Partition by brand) sum_flag, count(*) over(Partition by brand) total_count from cte ) a where sum_flag = total_count
select transaction_date, count(distinct users_id) no_of_users ,count(distinct product_id) no_of_products from ( select *, rank() over(partition by users_id order by transaction_Date desc) rnk from user_transactions ) a where rnk = 1 group by transaction_date
C
Correct 💯
In hp laptop same is not applicable pls tell me
In all laptop...this should work
--------------------CAN BE ACHEIVED EASILY BY ROW_NUMBER() with cte as(select *,row_number() over(partition by deptno order by salary)as rn from Employee_2), cte2 as( select *,row_number() over(partition by deptno order by salary desc)as rn2 from Employee_2) select empname,deptname,deptno,salary from cte where rn=1 union all select empname,deptname,deptno,salary from cte2 where rn2=1
-------1st SOLUTION select id,product,min(sales) over(partition by product)as sales_new from sales1 ------------------------2ND SOLUTION select id,product,SUM(sales) over(partition by product order by sales rows between unbounded preceding and current row)as cumulative_sales from sales1
with cte as(select *, first_value(destination_phone_nbr) over(partition by source_phone_nbr)as f_value, last_value(destination_phone_nbr) over(partition by source_phone_nbr)as l_value from Phone_Log), final_cte as( select source_phone_nbr, case when f_value=l_value then 'Y' else 'N' end as is_match,row_number() over(partition by source_phone_nbr)as rn from cte) select source_phone_nbr,is_match from final_cte where rn=1
WE MAY ACHEIVE THIS USING RECURSIVE CTE----- with recursive cte as(select min(id)as bs from sample_table union all select bs+1 from cte where bs+1<=(select max(id)from sample_table)) select * from cte where bs not in (select id from sample_table)
resolving the same question, and tried a little different approach.. luckily it works :) sol: with cte as ( select users_id,max(transaction_date) as dates,ROW_NUMBER() over(PARTITION by max(transaction_date) order by max(transaction_date)) as rn FROM user_transactions group by users_id ), cte2 as ( select transaction_date,count(transaction_date) as no_of_products from user_transactions group by transaction_date ) select cte.dates,max(cte.rn) as no_of_users,cte2.no_of_products from cte join cte2 on cte.dates=cte2.transaction_date group by cte.dates,cte2.no_of_products
SQL Query looks good to me ! Thanks for posting.
Many Many thanks for this video ❤
You are welcome 😍
the answer can achieved by this method also---------------- with cte as(select *,max(tranamt) over(partition by custid)as max_sal_per_cust from transaction_tbl) select *,round((tranamt/max_sal_per_cust),2)as ratio from cte
Yes, you are correct ! Thanks for posting.
Thank you sir
You are welcome 😍
very good explanation.
Thank you 😍
without CTE SELECT From_User,COUNt(*) cnt,row_number() OVER (ORDER BY COUNT(*) DESC, From_User) rnk FROM google_gmail_emails GROUP BY From_User ORDER BY COUNT(*) DESC
Why are we using [ ] brackets in the query? what is it's purpose?
You are really great. These videos helps lot❤
Thank you ! I am glad that my videos are helping you so much.
This SQL interview playlist really helped me and learned a lot. thank you great work!!
I am glad that my you tube channel helped you to learn a lot 😍😍 Share it with your friends circle so that it reaches to wider audience. Thank you !
Option 3
Correct 💯
with cte as (select len('interview') as rnk union all select rnk-1 from cte where rnk-1>0 ) select SUBSTRING('interview',1,rnk) from cte
select a.AccountNumber,b.Transaction_id, b.balance, a.trans_time from transaction_table b join( select accountNumber, max(transaction_time) as trans_time from transaction_table group by accountNumber ) as a on a.accountNumber= b.accountNumber and b.Transaction_time= a.trans_time order by Transaction_ID
Select Sales_Date,sum(Total) as Net from ( select Main.*,ROW_NUMBER () over (partition by Main.Sales_Date,Source_Currency order by Main.Effective_Start_Date desc) as RN from ( select Sales_Date,Effective_Start_Date,Source_Currency,Target_Currency,Sales_Amount,Exchange_Rate,(Sales_Amount*Exchange_Rate) as Total from T1, T2 where T1.Sales_Date >= T2.Effective_Start_Date and T1.Currency = T2.Source_Currency ) as Main) as sub where RN = 1 group by Sales_Date
Kindly check with this approach WITH t1 AS (SELECT ID, StudentName, CASE WHEN ID%2 =0 THEN ID/2 ELSE ID/2 + ID%2 END AS r FROM SeatArrangement), t2 AS (SELECT *,row_number() OVER (PARTITION BY r ORDER BY ID DESC) rn FROM t1) SELECT StudentName,row_number() OVER (ORDER BY 1) ID FROM t2
with cte as ( select *, row_number() over(partition by grp order by sequence) as rnk, sequence-row_number() over(partition by grp order by sequence) diff from emp) select distinct(grp) , min(sequence) over(partition by diff ) as minseq, max(sequence) over(partition by diff) as maxseq from cte order by grp;
with cte as( select *,max(tranamt) over(partition by custid) as mx_amt from Transaction_Tbl) select custid,tranid,tranamt,mx_amt,(tranamt/mx_amt) as ratio from cte;
Thank you❤🙏
You are welcome 😍
Thank you❤🙏
You are welcome😍
Select * from ( Select Student_Name, Total_Marks, Year, Lag(Total_Marks) over(partition by Student_Name order by Year) as Prev_Marks from Student ) A where Total_Marks >= Prev_Marks I tried this code and got the same results. Are there any test cases that might fail with this?
Very Interesting Question and Good Explanation 😃
Thanks a lot 😊
Thanks for this video sir
You are welcome !
@@ItJunction4all Sir how much sql one should to enter in data analytics field?
You should have good command in SQL so that you can do better data analysis in Data Analytics field. Try to solve problems in my SQL playlist that is more than enough.
@@ItJunction4all Thanks a lot sir for your suggestion. Thanks for sparing your time to reply me.. 🙏
No Problem :)
Good explanation. Regular upload more interview questions
Thank you! Will upload similar videos regularly
What will the results look like if you improve marks every year? Will you have multiple rows per student?