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

---Write a query to find out the cities where covid cases are increasing continuously.  create table covid(city varchar(50),days date,cases int); delete from covid; insert into covid values('DELHI','2022-01-01',100); insert into covid values('DELHI','2022-01-02',200); insert into covid values('DELHI','2022-01-03',300); insert into covid values('MUMBAI','2022-01-01',100); insert into covid values('MUMBAI','2022-01-02',100); insert into covid values('MUMBAI','2022-01-03',300); insert into covid values('CHENNAI','2022-01-01',100); insert into covid values('CHENNAI','2022-01-02',200); insert into covid values('CHENNAI','2022-01-03',150); insert into covid values('BANGALORE','2022-01-01',100); insert into covid values('BANGALORE','2022-01-02',300); insert into covid values('BANGALORE','2022-01-03',200)...

    What is INDEXING in SQL? 📢   ✔️ Indexes are special lookup tables that the database search engine can use to speed up data retrieval. ✔️ An index in a database is very similar to an index in the back of a book. For example, if you want to reference all pages in a book that discusses a certain topic, you first refer to the index, which lists all the topics alphabetically and are then referred to one or more specific page numbers. ✔️ An index helps to speed up SELECT queries and WHERE clauses, but it slows down data input, with the UPDATE and the INSERT statements. 🔎 When should Indexes be created? - A column contains a wide range of values, and table has millions of rows. - A column does not contain a large number of null values. - One or more columns are frequently used together in a where clause or a join condition. 🔎 When should indexes be avoided? - The table is small. - The columns are not often used as a condition in the query. - The column is ...

  ETL vs ELT ⚡ ETL is a traditional process for data transformation into a data warehouse (Cloud based or On-prem). ELT is a modern process for any kind of structured and non-structured data transformation inside a cloud based data warehouse. ⚡ In ETL, data moves from source to stage, then into data warehouse. In ELT, data staging is not required. Data can directly move into data warehouse from source. ⚡ ETL only transforms and loads the Data which you think is necessary when creating DWH. Hence Data migration is slower. ELT loads all the Data immediately into DWH and users can decide later which Data to transform and analyze. Hence Data migration is faster. ⚡ ETL is not normally a solution for Data lakes. It transforms data for integration with structured and relational DWH. ELT offers a pipeline for Data lakes to ingest any kind of structured or unstructured data. Data could be transformed on need basis for analysis. ⚡ ETL is best suited for dealing with smaller datasets. ELT i...

Write a SQL Query TO FIND THE AVERAGE PRICE OF EACH PRODUCT  -------------------START-----------------------------------------------------------------------   CREATE TABLE Price ( product_id INT NOT NULL, start_date DATE, end_date DATE, price INT ) insert Price values (1,GETDATE()-90,GETDATE()-70,100) insert Price values (1,GETDATE()-300,GETDATE()-100,120) insert Price values (2,GETDATE()-400,GETDATE()-300,220) insert Price values (2,GETDATE()-600,GETDATE()-500,320) SELECT * FROM Price SELECT * FROM Unitsold CREATE TABLE Unitsold ( product_id INT NOT NULL, purchase_date DATE, units INT) insert  Unitsold values(1,GETDATE()-80,22) insert  Unitsold values(1,GETDATE()-200,30) insert  Unitsold values(2,GETDATE()-340,40) insert  Unitsold values(2,GETDATE()-540,50) SELECT * FROM Price SELECT * FROM Unitsold ---------------------------------------------------SOLUTION--------------------------------- with CTE_1 as( select product_id,units,units*price as product_pric...

 ---------- Write a SQL Query who all are present inside the hospital--- -------------------------------------- create table hospital ( emp_id int , action varchar(10) , time datetime); insert into hospital values ('1', 'in', '2019-12-22 09:00:00'); insert into hospital values ('1', 'out', '2019-12-22 09:15:00'); insert into hospital values ('2', 'in', '2019-12-22 09:00:00'); insert into hospital values ('2', 'out', '2019-12-22 09:15:00'); insert into hospital values ('2', 'in', '2019-12-22 09:30:00'); insert into hospital values ('3', 'out', '2019-12-22 09:00:00'); insert into hospital values ('3', 'in', '2019-12-22 09:15:00'); insert into hospital values ('3', 'out', '2019-12-22 09:30:00'); insert into hospital values ('3', 'in', '2019-12-22 09:45:00'); insert into hospit...

------------- --Pareto Principle (80-20 Rule) Implementation in SQL- ------------------------------- ---------------START--------------------------------------------------------------- with CTE1 as ( select SSO.Product_ID,SUM (SSO.Sales) as 'Product Sales' FROM  [dbo].[Sample - Superstore_Orders] SSO GROUP BY SSO.Product_ID ),Sales_cal as ( select Product_ID,Sales,sum (Sales) over (ORDER BY Sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING ) as 'Running Sales',SUM (Sales) OVER ()* 0.8 as"Total Sales" FROM  [dbo].[Sample - Superstore_Orders] ) select * from Sales_cal WHERE Sales_cal.[Running Sales] <=Sales_cal.[Total Sales] ------END----------------------------------------------------------