![Code-Con](/img/default-banner.jpg)
- 32
- 40 594
Code-Con
Приєднався 8 жов 2023
Your one stop solution for your coding problems and building concepts.
SQL String Interview Question | Interview Question 22
Hey guys here with a string sql interview question. Do give it a try and share it in comments.
SQL Script :
INSERT INTO labemp (ID, Name, Designation)
VALUES
(1, 'ABC', 'Scientist'),
(2, 'PQR', 'Engineer'),
(3, 'STU', 'Analyst');
select * from labemp;
#dataanalytics #sql #dataengineer #amazon #meta #facebook #placement
SQL Script :
INSERT INTO labemp (ID, Name, Designation)
VALUES
(1, 'ABC', 'Scientist'),
(2, 'PQR', 'Engineer'),
(3, 'STU', 'Analyst');
select * from labemp;
#dataanalytics #sql #dataengineer #amazon #meta #facebook #placement
Переглядів: 167
Відео
SQL Interview Question | SQL Intermediate Question 22
Переглядів 20821 день тому
Question - Return schools and classes with atleast one student enrolled in Maths and Physics. DDL :- drop table if exists students CREATE TABLE Students ( StudentID INT NOT NULL PRIMARY KEY, School VARCHAR(100) NOT NULL, Class VARCHAR(50) NOT NULL ); CREATE TABLE Enrollments ( StudentID INT NOT NULL, Subject VARCHAR(100) NOT NULL ); INSERT INTO Students (StudentID, School, Class) VALUES (1, 'Gr...
Complex SQL Interview Question | Based on joins, aggregate functions and more
Переглядів 3512 місяці тому
Hey all, up with a tricky yet interesting sql interview question. Give it a try. Question- Number of times each student appeared for the exams. DDL Query: CREATE TABLE Examinations ( student_id INT, subject_name VARCHAR(50) ); Insert data into Examinations table INSERT INTO Examinations (student_id, subject_name) VALUES (1, 'Math'), (1, 'Physics'), (1, 'Programming'), (2, 'Programming'), (1, 'P...
IBM INTERVEW QUESTIO | SQL Intermediate Question 22
Переглядів 3942 місяці тому
Question Find the maximum number of offers each family can avail. Condition the family size should fall in the range of the countries. CREATE TABLE FAMILIES ( ID VARCHAR(50), NAME VARCHAR(50), FAMILY_SIZE INT ); Insert data into FAMILIES table INSERT INTO FAMILIES (ID, NAME, FAMILY_SIZE) VALUES ('c00dac11bde74750b4d207b9c182a85f', 'Alex Thomas', 9), ('eb6f2d3426694667ae3e79d6274114a4', 'Chris G...
SQL Interview Question | Intermediate SQL Question 21
Переглядів 3142 місяці тому
Question - Find number of members in a team for each employee. drop table Employee_Team CREATE TABLE Employee_Team ( employee_id INT, team_id INT ); INSERT INTO Employee_Team (employee_id, team_id) VALUES (1, 8), (2, 8), (3, 8), (4, 7), (5, Null), (6, 9); select * from Employee_Team . . . . . . #amazon #ai #meta #facebook #instagram #netflix #dataanalytics #sql #placement #college
TRICKY SQL Interview Question | SQL Intermediate Question 20
Переглядів 7422 місяці тому
Question - Write an SQL query 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐭𝐱𝐧𝐦𝐨𝐧𝐭𝐡 𝐰𝐡𝐢𝐜𝐡 𝐡𝐚𝐬 𝐭𝐡𝐞 𝐦𝐚𝐱𝐢𝐦𝐮𝐦 𝐭𝐱𝐧𝐚𝐦𝐨𝐮𝐧𝐭 The approach is to arrange the data vertically so that applying aggerate functions becomes easy. Comment below yours approach too. create table eshop(txnmonth varchar(50),clothing int,electronics int,sports int); insert into eshop values('Jan',2000,1500,3000); insert into eshop values('Feb',1000,2500,4000); inser...
ONE SHOT Concept on SQL JOINS
Переглядів 1264 місяці тому
This video covers the practical aspects of one of the most asked topis in interviews i.e. JOINS. Here i have implemented all the five joins in SQL and explained them. Do like, share and subscribe. #sqldeveloper #sql #dataanalytics #dataengineering #placement #college #interviewquestions #sqlinterviewquestionsandanswers #facebook #amazon #flipkart #myntra #tcs #deloittejobs #accenture
DELOITTE SQL Interview Question | SQL Intermediate Question 19
Переглядів 2,9 тис.4 місяці тому
Question - Find the products whose sales are increasing every year. CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(50), category VARCHAR(50) ); INSERT INTO products (product_id, product_name, category) VALUES (1, 'Laptops', 'Electronics'), (2, 'Jeans', 'Clothing'), (3, 'Chairs', 'Home Appliances'); CREATE TABLE sales ( product_id INT, year INT, total_sales_revenue DECI...
Tricky SQL Question | SQL Intermediate Question 18
Переглядів 4024 місяці тому
This is a tricky sql question where we need to map the children with their respective parents. ddl commands :- create table people(id int primary key not null, name varchar(20), gender char(2)); create table relations(c_id int, p_id int, FOREIGN KEY (c_id) REFERENCES people(id), foreign key (p_id) references people(id) ); insert into people (id, name, gender) values (107,'Days','F'), (145,'Hawb...
SQL Interview Question | SQL Intermediate Question
Переглядів 4105 місяців тому
Question - Write an SQL query 𝐭𝐨 𝐟𝐢𝐧𝐝 𝐭𝐡𝐞 𝐜𝐨𝐮𝐧𝐭 𝐨𝐟 𝐝𝐢𝐬𝐭𝐢𝐧𝐜𝐭 𝐝𝐞𝐩𝐚𝐫𝐭𝐦𝐞𝐧𝐭𝐬 𝐢𝐧𝐜𝐥𝐮𝐝𝐢𝐧𝐠 𝐍𝐔𝐋𝐋. drop table department create table department(deptid int, deptname varchar(50)); insert into department values(1,'Tech'); insert into department values(2,'HR'); insert into department values(3,null); insert into department values(4,'Tech'); insert into department values(5,'HR'); select * from department; #da...
GOOGLE SQL Interview Question | SQL Intermediate Question 16
Переглядів 3265 місяців тому
Write a query to calculate the sum of odd-numbered and even-numbered measurements separately for a particular day and display the results in two different columns. Refer to the Example Output below for the desired format. Definition: Within a day, measurements taken at 1st, 3rd, and 5th times are considered odd-numbered measurements, and measurements taken at 2nd, 4th, and 6th times are conside...
MICROSOFT SQL Interview Question | SQL Intermediate Question 15
Переглядів 3115 місяців тому
This is a video solution of an Interview Question asked by Microsoft . I have used Datalemur for this problem. Link to the question below :- datalemur.com/questions/supercloud-customer In the upcoming videos i will share more much question. #microsoft #meta #facebook #amazon #netflix #ai #placement #dataanalytics #sqldeveloper #interviewquestions #sql
PWC Interview Question for Data Analyst Role | SQL Intermediate Question 14
Переглядів 5066 місяців тому
Hey all, this question was asked in a PWC interview and u may encounter similar question in any data related role. Question - For each month find the candidate with highest number of votes. DDL Commands :- create table elections( candidate_id int, voter_id int, vote_date date) insert into elections values(1,01,'2023-12-01'), (1,02,'2023-12-05'), (1,03,'2023-12-02'), (2,01,'2023-12-01'), (1,01,'...
LeetCode SQL Interview Question | Find Second Highest Salary | Part 2
Переглядів 2326 місяців тому
Hey guys in this series we are solving leetcode top 50 interview questions. In this video I am solving a question to find the second highest salary using subquery. Link to Top 50 Questions leetcode.com/studyplan/top-sql-50/ #placement #leetcode #sql #sqldeveloper #dataanalytics #dataengineering #amazon #facebook #netflix #meesho #flipkart
FAANG SQL Interview Question | SQL Intermediate Question 13
Переглядів 1,1 тис.6 місяців тому
Hey guys, in this video i am solving a FAANG Interview question. Do try it by yourself and comment bellow your answers. DDL Commands :- create table clocked_hours( empd_id int, swipe time, flag char) insert into clocked_hours values (11114,'08:30','I'), (11114,'10:30','O'), (11114,'11:30','I'), (11114,'15:30','O'), (11115,'09:30','I'), (11115,'17:30','O'); #faang #facebook #apple #amazon #netfl...
L &T Infotech SQL Interview Question | SQL Intermediate Question 12
Переглядів 6 тис.6 місяців тому
L &T Infotech SQL Interview Question | SQL Intermediate Question 12
LEETCODE SQL Interview Question | PART 1
Переглядів 4116 місяців тому
LEETCODE SQL Interview Question | PART 1
Find Transition Point using C++ | Amazon Interview Question
Переглядів 536 місяців тому
Find Transition Point using C | Amazon Interview Question
PWC Data Analyst Interview | SQL Intermediate Question 11
Переглядів 17 тис.6 місяців тому
PWC Data Analyst Interview | SQL Intermediate Question 11
AMAZON DATA ENGINEER Interview Question | SQL Intermediate Question 10
Переглядів 9327 місяців тому
AMAZON DATA ENGINEER Interview Question | SQL Intermediate Question 10
AMAZON Interview Question | SQL Intermediate Question 9
Переглядів 8077 місяців тому
AMAZON Interview Question | SQL Intermediate Question 9
GOOGLE Interview Question | SQL Intermediate Question 8
Переглядів 2517 місяців тому
GOOGLE Interview Question | SQL Intermediate Question 8
Complex Question on Recursive CTE | SQL Intermediate Question 7
Переглядів 4828 місяців тому
Complex Question on Recursive CTE | SQL Intermediate Question 7
Recursive CTE | SQL Interview Question | Intermediate Level Concept
Переглядів 4338 місяців тому
Recursive CTE | SQL Interview Question | Intermediate Level Concept
Google Interview Question | SQL Intermediate Question 6
Переглядів 1,9 тис.8 місяців тому
Google Interview Question | SQL Intermediate Question 6
Customer Retention and Churn Analysis | Part 2 | SQL Intermediate Question 6
Переглядів 5408 місяців тому
Customer Retention and Churn Analysis | Part 2 | SQL Intermediate Question 6
Customer Retention and Churn Analysis | Part 1 | SQL Intermediate Question 5
Переглядів 8138 місяців тому
Customer Retention and Churn Analysis | Part 1 | SQL Intermediate Question 5
Delete Duplicate records from a table using 2 ways
Переглядів 468 місяців тому
Delete Duplicate records from a table using 2 ways
SQL Intermediate Question 4 - lag function and finding difference for each day
Переглядів 5549 місяців тому
SQL Intermediate Question 4 - lag function and finding difference for each day
SQL Intermediate Level Question 3 - Find 3rd highest salary with a twist.
Переглядів 9239 місяців тому
SQL Intermediate Level Question 3 - Find 3rd highest salary with a twist.
with cte as( select *,iif(lag(total_sales_revenue,1,total_sales_revenue)over(Partition by product_id order by year)<=total_sales_revenue ,1,0) As isincreasing from #sales) select Distinct Product_id from cte a where isincreasing=all(select isincreasing from cte where product_id=a.product_id)
with cte as (select s.sid,'new in source' as comment from sources s left join targets t on s.sid=t.tid where t.tid is null union all select t.tid,'Mismatch' as comment from targets t join sources s on t.tid=s.sid where t.tname<>s.sname union all select t.tid,'new in Target' as comment from targets t left join sources s on t.tid=s.sid where s.sid is null ) select * from cte
with cte as ( select company_id,user_id from company_users where language in ('English','German') group by user_id,company_id having count(language)=2 ) select company_id FROM cte group by company_id having count(user_id)>=2;
my approach in mysql : with fathers as (select p.id,p.name as father_name,r.c_id from people p inner join relations r on p.id=r.p_id where p.gender='M'), mothers as ( select p.id,p.name as mother_name,r.c_id from people p inner join relations r on p.id=r.p_id where p.gender='F') select mother_name,father_name,pl.name as child_name from mothers m join fathers f on m.c_id=f.c_id join people pl on f.c_id=pl.id order by pl.name;
Here is my approach : with cte as (select team_id as team_id,count(1) as team_cnt from employee_team group by 1) select employee_id,ifnull(team_cnt,0) as team_size from cte c right join employee_team e on c.team_id=e.team_id;
not sure if i understood the problem correctly by the example but i think this works perfectly fine w/o multiple ctes: "SELECT TOP 1 txnmonth FROM eshop ORDER BY clothing + electronics + sports DESC"
--Find the company who have alteast 2 users who speaks both English and German. with cte as ( select company_id,USER_ID,language, DENSE_RANK()over(partition by USER_ID order by language)as rn, count(language)over(partition by USER_ID)as cnt from Google_Intermediate_Interview where language in ('English', 'German') ) select company_id,USER_ID,language from cte where cnt >= 2
My approach : with cte as (select *,lead(flag) over(partition by empd_id order by swipe) as next_flag, lead(swipe) over(partition by empd_id order by swipe) as next_log from clocked_hours) select empd_id,cast(sum(case when flag='I' and next_flag='O' then cast(next_log-swipe as time) else 0 end) as time) as total_clocked_hrs from cte group by 1;
Nice✌️
so much background noise.🤷♂🤷♂
will try to improve
WITH cte AS (select student_id, school, class, subject from students where subject IN ('maths','physics')) select school, class, COUNT(DISTINCT student_id) AS student_count from cte where student_id IN (select student_id from cte group by student_id having COUNT(subject) >=2) group by school, class having student_count >=1; This will be the answer
select * from products where product_id not in ( select distinct product_id from ( select *, coalesce(total_sales_revenue-lag(total_sales_revenue) over(partition by product_id order by year asc),0) as lag_col from sales)a where a.lag_col<0 )
We can create case statement as well right? If current destination > Prev destination then 1 else 0 .. And then filter the result where we have only 1 Kindly let me know if this works or not.. Many thanks ❤
Thanks for the video. I used the lag function and used distinct instead of max in the output line. Is it valid? Please check. with cte as ( select p.product_id, p.product_name, s.year, total_sales_revenue, LAG(total_sales_revenue,1) over (partition by p.product_id order by year) as prev_year_revenue from products p join sales s on p.product_id = s.product_id --order by p.product_id, s.year ) select distinct product_id, product_name from cte where product_id not in (select product_id from cte where total_sales_revenue < prev_year_revenue)
I used self join: select p.name, max(case when pp.gender = 'F' then pp.name end) Mother, max(case when pp.gender = 'M' then pp.name end) Father from people p join relations r on p.id = r.c_id join people pp on pp.id = r.p_id group by p.name Thanks for the video.
with cte as ( select empNo,eName,sal,deptno , max(sal) over (partition by deptno) as max_sal , min(sal) over (partition by deptno) as Min_sal from emp2) select c1.empNo,c1.eName,c1.deptno,c2.max_sal,c2.min_sal from cte c1 join cte c2 on c1.empno=c2.empno where c1.sal= c2.max_sal or c1.sal=c2.min_sal order by c1.deptno
Select taxmonth, sum(cast(clothing as int)+cast(electronics as int)+cast(sports as int)) as total_sales From eshop Group by taxmonth order by total_sales desc limit 1 this als works
other method----- select st.student_id ,st.student_name ,s.subject_name ,NULLif(count(e.subject_name),0) as No_of_times_appeared from Students st cross join Subjects s left join Examinations e on e.student_id = st.student_id and e.subject_name = s.subject_name group by st.student_id,st.student_name,s.subject_name order by st.student_id
select sales_date, sum(case when fruits='apples' then sold_num end) apple_count, sum(case when fruits='oranges' then sold_num end) orange_count, sum(case when fruits='apples' then sold_num end) - sum(case when fruits='oranges' then sold_num end) diff_count from sales group by sales_date
Are the two users 1 and 3 who are working in company 1? Does this correct?
yeah
Nice explanation,and good question though ✅
select [dep_id],max([salary]) as higest_salary, min([salary]) as lowest_salary from [Sambit].[dbo].[emp] group by [dep_id]
select distinct cust_id from (select * ,min(order_date) over(partition by cust_id order by order_date) as min_date ,max(order_date) over(partition by cust_id order by order_date desc) as max_date from transactions)a where (month(max_date)-month(min_date))=1
select * from( select sid , case when sname <> tname then 'Mimatched' when tid is null then 'New in sources' end Review from sources as s left join targets t on s.sid = t.tid) t1 where Review is not null union select tid , 'New in targets' from targets where tid not in( select sid from sources) is this correct solution?
copy past question and solution, why you do like this do some real good question
select txnmonth from eshop where clothing + electronics + sports = ( select max(clothing + electronics + sports) from eshop);
Thanks for your content. Can you please add below in description which would help create table merchant(merchant_id varchar(20) , amount int, payment_mode varchar(20)); insert into merchant values ('m1',200,'cash'),('m2',520,'online'),('m1',700,'online'),('m3',1400,'online'),('m2',50,'cash'),('m1',300,'cash'); select * from merchant;
select user_id,company_id from ( select count(rn) as cunt,user_id,company_id from ( select company_id,user_id,language,rank() over (partition by company_id order by user_id ) rn from company_users where language in('english','german') ) as a group by user_id,company_id ) as b where cunt >=2
SELECT D.dep_Name, MAX (salary) AS highest_salary, MIN(Salary) AS lowest_salary FROM EMployees E LEFT JOIN DEPARTMENTS D ON E.DEP_ID = D.DEPT_ID_DEP GROUP BY D.DEP_NAME Please let me know is it correct or wrong
with cte as (select *, lead(Fruits) over(partition by Sales_date order by sales_date) le_fu111, lead(sold_num) over(partition by Sales_date order by sales_date) le_fu11 from sales) select *, sold_num-le_fu11 from cte where le_fu11 is not null;
emp id, 4,7 are not 3rd highest salary
I got emp id--2, 6,9,10
with cte as ( select *, count(dep_id) over(partition by dep_id ) as dep_count, ROW_NUMBER() over(partition by dep_id order by emp_salary ) as rn from employees ) select * from cte where dep_count >=3 and rn=3 union select * from cte where dep_count <3 and rn=1 order by rn desc
Cant we do this Select taxmonth, Max(clothing+electronics+sports) From eshop Group by 1
no this is syntax wise all instead you can use this Select taxmonth, sum(cast(clothing as int)+cast(electronics as int)+cast(sports as int)) as total_sales From eshop Group by taxmonth order by total_sales desc limit 1
My solution with cte as ( SELECT * ,LEAD(total_sales1_revenue,1) OVER(PARTITION BY product_id ORDER BY year) Year1 ,LEAD(total_sales1_revenue,2) OVER(PARTITION BY product_id ORDER BY year) year2 ,ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY year) as RW FROM sales1 ) ,cte2 as( SELECT *, CASE WHEN (total_sales1_revenue < Year1 ) AND (Year1 < Year2) THEN 1 ELSE 0 END flag FROM cte WHERE RW = 1 ) SELECT P.* FROM products1 P JOIN cte2 C ON P.product_id=C.product_id WHERE C.flag = 1
Thanks for posting the problem along with data set
Simple Query select Dep_id,avg(salary) as avg_salary,min(salary) as Min_salary, Max(salary) as max_salary from table name group by Dep_id order by dep_id
Can we do partition on Payment mode and seperate the online and cash mode with row number as 1 for cash and row number as 2 for online.... And then case statement in which sum of R1 gives the value and puts 0 for online. Similarly another case statement in which sum of R2 gives the value and puts 0 for cash. Please let me know if this approach is correct or not.
with cte as (select *,(lead(total_sales_revenue) over (partition by product_id order by year)-total_sales_revenue)as x from sales) select distinct c.product_id,p.product_name from cte c inner join productss p on c.product_id=p.product_id where c.product_id not in (select product_id from cte where x<0) my solution
can we do it without using a CTE? i suspect we can , though unlikely to be optimal, thoughts??
Try it out
Bro, Please post the string based scenario questions
with cte as (select *,count(team_id) over(partition by team_id) [count] from Employee_Team) select employee_id,count from cte order by employee_id; thanks bro! i have solved it ony own, by seeing your past practice videos. I know i have solved very easy one, but i understand and enjoyed this. it took me 4 attempts to achieve this.
Keep it up bro!!
explanation 👌
My solution with cte as (select empd_id , swipe_time , flag as f , lag(swipe_time) over(partition by empd_id) as new_time from clocked_hours) select empd_id , extract(hour from sum(swipe_time - new_time)) as clicked_hrs from cte where f = 'O' group by empd_id
my solution in mssql DB : with child as (select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id union select p1.*,r1.p_id from people as p1 inner join relations as r1 on p1.id=r1.c_id ), main as ( Select child.name as child_name ,people.name as perent_name , people.gender from child inner join people on child.p_id=people.id ) Select child_name ,max(case when gender ='F' then perent_name else null end) as father ,max(case when gender ='M' then perent_name else null end) as mother from main group by child_name
with cte as(select *,lag(sold_num) over(partition by sales_date order by sales_date) as no_of_fruits, row_number() over(partition by sales_date order by sales_date desc) as rn from sales), cte2 as (select *,case when rn=2 then abs(sold_num-no_of_fruits) else False end as otpt from cte) select * from cte2 where otpt != 0
with cte as (select emp_name,emp_salary,dep_id,count(*) over(partition by dep_id) as cnt, rank() over(partition by dep_id order by emp_salary desc) as rnk from employees), output_case as(select emp_name,emp_salary,dep_id,cnt,rnk, case when cnt=4 then rnk=3 else null end as final, case when cnt=3 then rnk=3 else null end as final2, case when cnt=2 then rnk=2 else null end as final3, case when cnt=1 then rnk=1 else null end as final4 from cte) select emp_name,emp_salary,dep_id from output_case where final=1 or final2=1 or final3=1 or final4=1 I KNOW this code is bit lengthy but still solves the purpose easily
this is a complex method, only works for this particular data, static methods are not good and useful for dynamic data updates
same solution as you in MSSQL DB Select merchant_id ,sum(case when payment_mode = 'cash' then amount else 0 end ) as Cash ,sum(case when payment_mode = 'online' then amount else 0 end ) as Online from payments group by merchant_id order by sum(case when payment_mode = 'cash' then amount else 0 end) desc
my solution on MSSQL DB: with cte as ( Select * ,case when total_sales_revenue< lead(total_sales_revenue,1, total_sales_revenue+1)over(partition by product_id order by year) then 1 else null end as new from sales ), sales_cte as( Select * from sales where product_id not in (select product_id from cte where new is null) ) select products.* from sales_cte inner join products on sales_cte.product_id = products.product_id group by products.product_id, products.product_name, products.category
with cte as( select candidate_id,datepart(year,vote_date)*100+''+datepart(month,vote_date) as weeks ,count(voter_id) counts from elections group by datepart(year,vote_date)*100+''+datepart(month,vote_date),candidate_id ),cte2 as( select * ,RANK() OVER(PARTITION BY weeks order by counts desc)rn from cte) select * from cte2 where rn=1;