--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 'Activity_RANK'  FROM UserActivity U
)A
WHERE A.Activity_RANK=2 
UNION ALL
SELECT B.username,B.activity,B.startDate,B.endDate FROM UserActivity B WHERE B.username IN 
(
SELECT Z.username as 'Activity_COUNT_Z'  FROM UserActivity Z
GROUP BY Z.username
HAVING COUNT (1)=1

)

SELECT REPLICATE('*',150) AS 'END OF SOLUTION'

Converting rows into columns BY PIVOT

 --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 IN ([Mumbai],[Bangalore],[Delhi]
)
)AS PivotTable

SELECT REPLICATE('*',150) AS 'SOLUTION START WITH OUT  PIVOT '

WITH tmp
     AS (SELECT *,
                Row_number()
                  OVER (
                    partition BY city
                    ORDER BY NAME ) rnk
         FROM   players_location)
SELECT rnk,
       Max(CASE
             WHEN city = 'Bangalore' THEN NAME
           END) Bangalore,
       Max(CASE
             WHEN city = 'Delhi' THEN NAME
           END) Delhi,
       Max(CASE
             WHEN city = 'Mumbai' THEN NAME
           END) Mumbai
FROM   tmp
GROUP  BY rnk 

SELECT REPLICATE('*',150) AS 'END OF SOLUTION'

------------------END OF THE SOLTION--------------------------

SQL to find the employee who got a salary increase for consecutive 2 years

 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)=YEAR (E3.YEARL)+1 AND E1.salary > E3.salary

INNER JOIN EMP_EY E4 ON YEAR (E1.YEARL)=YEAR (E4.YEARL)+2 AND E1.salary > E4.salary

ROWS BETWEEN AND RANGE BETWEEN WINDOW CLAUSE

---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 ROWS or RANGE clause
--2. When RANGE clause is used explicitly with it's default value
--3. When ROWS clause is used instead of RANGE clause
--ROWS BETWEEN doesn't care about the exact values. It cares only about the order of rows, and takes fixed number of preceding and following rows 
--when computing frame.
--RANGE BETWEEN cluase consider each row as entities 
--UNBOUNDED PRECEDING – All rows before the current row.
--n PRECEDING – n rows before the current row.
--CURRENT ROW – Just the current row.
--n FOLLOWING – n rows after the current row.
---UNBOUNDED FOLLOWING – All rows after the current row.

SELECT  ID, HIRE_DATE,Name, Salary, 
SUM(Salary) OVER(ORDER BY Salary ) as 'Running Total Default',

    SUM(Salary) OVER(ORDER BY Salary  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_ROWS,

SUM(Salary) OVER(ORDER BY Salary  DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal_RANGE,

AVG(Salary) OVER (ORDER BY Salary DESC  ROWS BETWEEN UNBOUNDED PRECEDING AND unbounded FOLLOWING) AS 'Running_Avg',

AVG(Salary) OVER (ORDER BY HIRE_DATE ROWS BETWEEN 2  PRECEDING AND CU
RRENT ROW ) AS 'Running_2_Avg',

AVG(Salary) OVER (ORDER BY HIRE_DATE ROWS BETWEEN 29 PRECEDING AND CURRENT ROW )as '30day_moving_average'

FROM Employees
ORDER BY Salary desc

Difference between count(*) and count(column_name)

 ---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 the null  ' 

 FROM [dbo].[emp_2020] E_20 FULL   JOIN 
 [dbo].[emp_2021] E_21
 ON E_20.emp_id=E_21.emp_id

 SELECT REPLICATE('*',200) AS 'END OF SOLUTION'

HIRE JUNIOR & SENIOR DEVELOPER WITHIN BUDGET SQL_INTREVIEW

 --#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 UNBOUNDED PRECEDING AND CURRENT ROW ) 'Running_Sum_Salary'
FROM candidates
),
SENIORS AS 
( SELECT * FROM TOTAL_SALARY  WHERE TOTAL_SALARY.Running_Sum_Salary <70000 AND TOTAL_SALARY.experience='Senior'
)
SELECT * FROM TOTAL_SALARY  WHERE   TOTAL_SALARY.experience='Junior' AND TOTAL_SALARY.Running_Sum_Salary < 70000 -  (SELECT SUM (SENIORS.salary) FROM SENIORS)
UNION ALL
SELECT * FROM SENIORS

SELECT REPLICATE('*',400) END_OF_Solution

------------------------------------------------END OF SOLUTION-------------------------------

WAQ to find out the EMP NAME, MANAGER,SENIOR MANAGER FROM EMPLOYEE TABLE

 --#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

values (10, 'Rakesh',500,7000,6,50);

SELECT * FROM emp_tbl

---------------------------------------START SOLUTION------------------------

SELECT REPLICATE('*',400) Solution

SELECT   E.emp_id,E.emp_name AS 'EMP NAME',M.emp_name AS 'Manager Name' ,SM.emp_name AS 'Senior Manager'   FROM emp_tbl E
 JOIN emp_tbl M ON E.manager_id=M.emp_id
 JOIN emp_tbl SM ON M.manager_id=SM.emp_id

 SELECT REPLICATE('*',400) as  'End of Solution'

 -------------------------------------END OF SOLUTION--------------------------

1+NULL SQL Intreview question

 ---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 -----------------------------------------------------------

WAQ TO Find out the new order ID inserted & deleted order id from last refresh copy

 #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' END as 'STATUS'   FROM 
(
SELECT tbl.order_id as 'Table_Order_id',tbl_c.order_id as 'Table_Copy_Order_ID' FROM tbl_orders tbl
RIGHT  JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) B WHERE B.Table_Order_id IS NULL 
UNION ALL 
SELECT A.Table_Order_id ,CASE WHEN A.Table_Copy_Order_ID IS NULL THEN 'I' else '0' END as 'STATUS'   FROM 
(
SELECT tbl.order_id as 'Table_Order_id',tbl_c.order_id as 'Table_Copy_Order_ID' FROM tbl_orders tbl
LEFT  JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) A WHERE A.Table_Copy_Order_ID IS NULL 

--------------------------END OF SOLUTION---------------------------------------

SQL query to find the total ride of a driver & profit ride

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', '13:30', '14:30', 'c','h');

----------------------------------------SAMPLE TABLE CREATED--------------------------
SELECT * FROM drivers
--------------------------------------------SOLUTION ----------------------------------------------------
SELECT REPLICATE('*',800) as'Solution'
-----------------------------------------
SELECT A.id ,count (1) as 'Total Ride',
SUM (case when end_loc=A.[Next_Ride_Location] THEN 1 ELSE 0 end ) AS 'Profit_Rides'
FROM 
(
SELECT * ,LEAD(start_loc,1) OVER (PARTITION BY id ORDER BY start_time )as 'Next_Ride_Location'   FROM drivers
) A
GROUP BY A.id

Deleted order id & inserted order id from last refresh copy

 --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_Copy_Order_ID'  FROM tbl_orders tbl
RIGHT   JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) B WHERE B.Table_Order_id IS NULL 

UNION ALL 

SELECT A.Table_Order_id ,CASE WHEN A.Table_Copy_Order_ID IS NULL THEN 'I' else '0' END as 'STATUS'      FROM 
(
SELECT tbl.order_id  as 'Table_Order_id',tbl_c.order_id as 'Table_Copy_Order_ID'  FROM tbl_orders tbl
LEFT   JOIN tbl_orders_copy tbl_c
on tbl.order_id=tbl_c.order_id --and tbl_c.order_id is null 
) A WHERE A.Table_Copy_Order_ID IS NULL 

--------------------------END OF SOLUTION---------------------------------------

UBER Interview for Data Engineer Analytics position._SQL_Intreview_Question

 -- 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');

----------------------------------------SAMPLE TABLE CREATED--------------------------

SELECT * FROM drivers

--------------------------------------------SOLUTION ----------------------------------------------------

SELECT A.id ,count (1) as 'Total Ride',
SUM (case when end_loc=A.[Next_Ride_Location] THEN 1 ELSE 0 end ) AS 'Profit_Rides'
FROM 
(
SELECT * ,LEAD(start_loc,1) OVER (PARTITION BY id ORDER BY start_time )as 'Next_Ride_Location'   FROM drivers
) A
GROUP BY A.id

No of recoreds on Inner join,left join,right join & full join

 --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.

SQL QUERY TO Highest and Lowest Salary Employees in Each Department-

 ----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--------------