--DAY 6 --#15DAYSCHALLENGE --Second most recent activity ,if there is only one activity is there it should also reflect  --Letscode QUESTION create table UserActivity ( username      varchar(20) , activity      varchar(20), startDate     Date   , endDate      Date ); insert into UserActivity values  ('Alice','Travel','2020-02-12','2020-02-20') ,('Alice','Dancing','2020-02-21','2020-02-23') ,('Alice','Travel','2020-02-24','2020-02-28') ,('Bob','Travel','2020-02-11','2020-02-18'); ------------------------------------SOLUTION----------------------------------------------------- SELECT REPLICATE('*',150) AS 'INPUT_TABLE' SELECT * FROM UserActivity SELECT REPLICATE('*',150) AS 'SOLUTION START' SELECT A.username,A.activity,A.startDate,A.endDate  FROM  ( SELECT  U.*,DENSE_RANK () OVER (PARTITION BY U.username ORDER BY U.startDate ) as...

 --DAY 6 --#15DAYS CHALLENGE -- Converting rows into columns BY PIVOT  --Without using PIVOT ------------------------------SAMPLE TABLE CREATED--------------------------------------- SELECT REPLICATE('*',150) AS 'SAMPLE TABLE CREATED' create table players_location ( name varchar(20), city varchar(20) ); --delete from players_location; insert into players_location values ('Sachin','Mumbai'),('Virat','Delhi') , ('Rahul','Bangalore'),('Rohit','Mumbai'),('Mayank','Bangalore'); SELECT REPLICATE('*',150) AS 'INPUT_TABLE' SELECT * FROM players_location ------------------------------------------------SOLUTION-------------------------------------------------------------- SELECT REPLICATE('*',150) AS 'SOLUTION START WITH PIVOT ' select * FROm  ( SELECT name,city,ROW_NUMBER() OVER (PARTITION BY City ORDER BY Name) AS rn FROM players_location ) A PIVOT( MAX(Name) FOR City...

 DAY 5 #15DAYSSQLCHALLENEGE ----EY INTERVIEW QUESTION---- --Write a SQL to find the employee who got a salary increase for consecutive 2 years  ----------------------------Table creation---------------- --create table EMP_EY ( emp_name varchar(10), dep_id int, salary int, YEARL DATETIME ); --DROP TABLE  EMP_EY; insert into EMP_EY values  ('Siva',1,30000,'2000'),('Siva',1,40000,'2001'),('Siva',1,50000,'2002'),('Arabinda',2,1000,'2010'), ('Arabinda',2,2000,'2011'),('Arabinda',2,3000,'2012'), ('Robert',3,100,'2012'),('Robert',3,102,'2015'),('Robert',3,105,'2017'), ('Sanvi',4,199,'2018'),('Sanvi',4,99,'1990'),('Sanvi',4,9,'1993') -------------------------------------------------Table creation --END ----------- SELECT * FROM EMP_EY SELECT E1.*  FROM EMP_EY E1 INNER JOIN EMP_EY E3 ON YEAR (E1.YEARL)=YEA...

---DAY 5 -- #15DAYSSQLCHALLENGE --DROP TABLE Employees Create Table Employees (  Id int primary key,  HIRE_DATE DATE,  Name nvarchar(50),  Salary int ) Go Insert Into Employees Values (1,'2022-12-21 12:53:27.120', 'Mark', 1000) Insert Into Employees Values (6, '2022-12-20 12:53:27.120' ,'ARABINDA', 1000) Insert Into Employees Values (6, '2022-12-2 12:53:27.120' ,'NAYAK', 1000) Insert Into Employees Values (2, '2022-12-8 12:53:27.120', 'John', 2000) Insert Into Employees Values (3, '2022-12-9 12:53:27.120', 'Pam', 3000) Insert Into Employees Values (4, '2022-12-11 12:53:27.120',  'Sara', 4000) Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000) Insert Into Employees Values (5, '2022-12-14 12:53:27.120', 'Todd', 5000) Go --Now execute the following query. Notice that we get the running total as expected. -- 1. When no value is specified for...

 ---DAY 4 --#15DAYSSQLCHALLENGE --Difference between count(*) and count(column_name) in SQL --Count(*) will count the null value --count(column_name) will not count the null value  ------------------------------START ------------------- SELECT REPLICATE('*',200) AS 'SOLUTION'  SELECT * FROM [dbo].[emp_2020]   SELECT * FROM  [dbo].[emp_2021] SELECT REPLICATE('*',200) AS 'JOIN THE ABOVE TWO TABLE WITH FULL OUTER JOIN '   SELECT *   FROM [dbo].[emp_2020] E_20 FULL   JOIN   [dbo].[emp_2021] E_21  ON E_20.emp_id=E_21.emp_id  SELECT  COUNT (* ) 'Count the no of records in the table' ,REPLICATE('*',200) AS 'COUNT (*) --count the null value '   FROM [dbo].[emp_2020] E_20 FULL   JOIN   [dbo].[emp_2021] E_21  ON E_20.emp_id=E_21.emp_id   SELECT  COUNT (e_21.designation) 'Count of designation'   ,REPLICATE('*',200) AS ' COUNT (COLUMN NAME)--Here it will not count t...

 --#15day SQL CHALLENGE --DAY 4 --We are having a budget of 70000 ,have to hire senior & junior developers within 70000. --But first we have to hire senior & then to hire junior with the remaining budget(Amount is remain after hiring senior developer ) --So write a sql query to find the employee (junior & senior developer ) those got hired within the above condition. SELECT REPLICATE('*',400) as 'Sample_Table_Created' create table candidates ( emp_id int, experience varchar(20), salary int ); delete from candidates; insert into candidates values (1,'Junior',10000),(2,'Junior',15000),(3,'Junior',40000),(4,'Senior',16000),(5,'Senior',20000),(6,'Senior',50000); SELECT * FROM candidates ---------------------------------------START SOLUTION------------------------ SELECT REPLICATE('*',400) Solution WITH TOTAL_SALARY AS ( SELECT *,SUM (salary) OVER (PARTITION BY experience order by salary asc  ROWS BETWEEN UN...

  --#15day SQL CHALLENGE --DAY 4 ---Follow my blog to get the question & answer asked by a  company. ----Write a SQL Query to find out the EMP NAME, MANAGER,SENIOR MANAGER FROM EMPLOYEE TABLE SELECT REPLICATE('*',400) as 'Sample_Table_Created' create table emp_tbl( emp_id int, emp_name varchar(20), department_id int, salary int, manager_id int, emp_age int); insert into emp_tbl values (1, 'Ankit', 100,10000, 4, 39); insert into emp_tbl values (2, 'Mohit', 100, 15000, 5, 48); insert into emp_tbl values (3, 'Vikas', 100, 12000,4,37); insert into emp_tbl values (4, 'Rohit', 100, 14000, 2, 16); insert into emp_tbl values (5, 'Mudit', 200, 20000, 6,55); insert into emp_tbl values (6, 'Agam', 200, 12000,2, 14); insert into emp_tbl values (7, 'Sanjay', 200, 9000, 2,13); insert into emp_tbl values (8, 'Ashish', 200,5000,2,12); insert into emp_tbl values (9, 'Mukesh',300,6000,6,51); insert into emp_tbl v...

 ---Product base interview question --Mention the output. -- # 15day SQL CHALLENGE --DAY-3 ---Please  Findout the below question & answer---- - --------------------------------------------- SELECT 1+NULL --NULL SELECT '1'+NULL --NULL SELECT '1'+'NULL' --1NULL SELECT 1+'NULL' --Conversion failed when converting the varchar value 'NULL' to data type int. -----------------------------------------END -----------------------------------------------------------

  #15day SQL CHALLENGE DAY-2 -- --The order table is there every day at 12AM IST , a copy of the table to be taken (Copy of Order table) . --So find out the new order ID inserted & deleted order id from last refresh copy  ---------------------------SAMPLE DATA CREATED---------- create table tbl_orders ( order_id integer, order_date date ); insert into tbl_orders values (1,'2022-10-21'),(2,'2022-10-22'), (3,'2022-10-25'),(4,'2022-10-25'); select * into tbl_orders_copy from tbl_orders; select * from tbl_orders; insert into tbl_orders values (5,'2022-10-26'),(6,'2022-10-26'); delete from tbl_orders where order_id=1; SELECT * FROM tbl_orders SELECT * FROM tbl_orders_copy --------------------------------------------SOLUTION----------------------- SELECT REPLICATE('*' , 200) as 'solution' ----------------------------------------- SELECT B.Table_Copy_Order_ID ,CASE WHEN B.Table_Order_id IS NULL THEN 'D' else '0...

15-day challenge DAY-1 SQL Question-1 Date-17TH DEC 2022 -- SQL interview question asked in UBER Interview for Data Engineer Analytics position.  ---Write a SQL query to find the total ride of a driver & profit ride (When the start location is the same as the end location for the constitutive  ride for each driver ) create table drivers(id varchar(10), start_time time, end_time time, start_loc varchar(10), end_loc varchar(10)); insert into drivers values('dri_1', '09:00', '09:30', 'a','b'),('dri_1', '09:30', '10:30', 'b','c'),('dri_1','11:00','11:30', 'd','e'); insert into drivers values('dri_1', '12:00', '12:30', 'f','g'),('dri_1', '13:30', '14:30', 'c','h'); insert into drivers values('dri_2', '12:15', '12:30', 'f','g'),('dri_2', '1...

 --The order table is there, every day at 12AM IST , a copy of the table is to be taken (Copy of Order table) . --To find out the new order ID  inserted & deleted order id from the last refresh copy  ---------------------------SAMPLE DATA CREATED---------- create table tbl_orders ( order_id integer, order_date date ); insert into tbl_orders values (1,'2022-10-21'),(2,'2022-10-22'), (3,'2022-10-25'),(4,'2022-10-25'); select * into tbl_orders_copy from  tbl_orders; select * from tbl_orders; insert into tbl_orders values (5,'2022-10-26'),(6,'2022-10-26'); delete from tbl_orders where order_id=1; SELECT * FROM tbl_orders SELECT * FROM tbl_orders_copy --------------------------------------------SOLUTION----------------------- SELECT B.Table_Copy_Order_ID ,CASE WHEN B.Table_Order_id IS NULL THEN 'D' else '0' END as 'STATUS'      FROM  ( SELECT tbl.order_id  as 'Table_Order_id',tbl_c.order_id as 'Table_Cop...

 -- SQL interview question asked in UBER Interview for Data Engineer Analytics position.  ---Write a SQL query to find the total ride of a driver & profit ride (When the start location is same as end location for the consititve  ride for each driver ) create table drivers(id varchar(10), start_time time, end_time time, start_loc varchar(10), end_loc varchar(10)); insert into drivers values('dri_1', '09:00', '09:30', 'a','b'),('dri_1', '09:30', '10:30', 'b','c'),('dri_1','11:00','11:30', 'd','e'); insert into drivers values('dri_1', '12:00', '12:30', 'f','g'),('dri_1', '13:30', '14:30', 'c','h'); insert into drivers values('dri_2', '12:15', '12:30', 'f','g'),('dri_2', '13:30', '14:30', 'c','h'); ------------...

 --QUestion-- There are 2 tables, the first table has 10 records and the second table has 5  records,Find out the no of records on LEFT JOIN,RIGHT JOIN,INNER JOIN & FULL OUTER JOIN.

 ---- L&T SQL Interview Problem | Print Highest and Lowest Salary Employees in Each Department---- ----- create table employee  ( emp_name varchar(10), dep_id int, salary int ); delete from employee; insert into employee values  ('Siva',1,30000),('Ravi',2,40000),('Prasad',1,50000),('Sai',2,20000) SELECT * FROM employee ---Sample Table Creation-------------------- ---Solution------------------------ WITH max_min_salary AS( SELECT *,max(salary)OVER(PARTITION BY dep_id)AS max_salary, min(salary)OVER(PARTITION BY dep_id)AS min_salary FROM employee) SELECT dep_id,max(CASE WHEN salary=max_salary THEN emp_name END) AS max_salary_employee,  max(CASE WHEN salary=min_salary THEN emp_name END) AS min_salary_employee FROM max_min_salary GROUP BY dep_id ------------------END OF THE SOLUTION--------------