Query to find out the cities where covid cases are increasing continuously.

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

insert into covid values('BANGALORE','2022-01-04',400);

SELECT * FROM covid

---Write a query to find out the cities whare covid cases are increasing continously.

--Solution----

--Approach

--1-finding the that each date wise covid cases are increasing 

--2-By using Rank function we can find that cases are increasing or not (By date wise also)

--3- Now when the both the rank is same (Means case wise and days wise its increasing )

--4--Want to reflect the city only so group by & taken the difference between both rank so it will be taken only ZEROS (No difference between rank

--5--Find the diff is 0 & count of all these diff should be 1.

WITH CTE_1 AS 

(

SELECT *, RANK() OVER (PARTITION BY city order by days) rn_days,

RANK() OVER (PARTITION BY city order by cases) rn_cases,RANK() OVER (PARTITION BY city order by days) - RANK() OVER (PARTITION BY city order by cases) AS DIFF

FROM covid

) 

SELECT city from CTE_1

GROUP BY city

having count(distinct (DIFF))=1 AND MAX(DIFF)=0 ---

---solution end---

INDEXING in SQLSERVER

  

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

📍Syntax to create an Index:

CREATE INDEX INDEX_SALES ON [Sample - Superstore_Orders]

(Order_ID)

 

📍Syntax to remove an Index:
DROP INDEX index;

📍Syntax to alter an Index:
ALTER INDEX INDEX_SALES ON  [Sample - Superstore_Orders]

REBUILD

ETL vs ELT

 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 is best suited for dealing massive amount of structured or unstructured data

⚡Use cases for ELT:
Organizations with huge amount of Data
Organizations which require immediate access of data

⚡Uses cases for ETL:

Organizations which need to synchronize data from multiple sources
Organizations which need to migrate and update their data from legacy systems

SQL Query TO FIND THE AVERAGE PRICE OF EACH PRODUCT -Intreview Question

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_price
 from(
select us.product_id,purchase_date,units,price from
Price p join Unitsold us on p.product_id=us.product_id
and purchase_date between start_date and end_date)A)
select product_id,sum(product_price)/sum(units) as 'Average_price'
from CTE_1 group by product_id

SQL Query who all are present inside the hospital-

 ----------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 hospital values ('4', 'in', '2019-12-22 09:45:00');

insert into hospital values ('5', 'out', '2019-12-22 09:40:00');

SELECT * FROM hospital 

with CTE1 as 
(SELECT emp_id,
max(case when action='in' then time END )as 'LoginTime',
max(case when action='out' then time END )as 'LogOUTTime'
FROM hospital 
group by emp_id
)
SELECT emp_id from CTE1 
WHERE LoginTime > LogOUTTime OR LogOUTTime IS NULL

------------END-----------------------

-Pareto Principle (80-20 Rule) Implementation in SQL||SQL INTREVIEW QUESTION

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

Product base SQL intreview question

---------Write a query to find PersonID,Name,Number of friends, and sum of marks of person who have friends with total score greater then 100-----------

-------------------START----------------------------------------------------------------------------

select * from [ Person_Table]

SELECT * FROM [dbo].[Friend]

WITH CTE1 AS
(
SELECT A.PersonID, count (A.PersonID) as "No of friends", SUM(B.Score) as "Total Marks"
FROM [dbo].[Friend] A
INNER JOIN [ Person_Table] B ON A.FriendID = B.PersonID
group by  A.PersonID
Having SUM(B.Score) >100
)
SELECT P.*,CTE1.[No of friends],CTE1.[Total Marks]
FROM [ Person_Table] P
INNER JOIN CTE1 ON CTE1.PersonID=P.PersonID

--------------END----------------------------------------------------------------------------------------------------

Laptop vs. Mobile Viewership SQL INTREVIEW QUESTION

 /*Assume that you are given the table below containing information on viewership by device type (where the three types are laptop, tablet, and phone). 

Define “mobile” as the sum of tablet and phone viewership numbers. Write a query to compare the viewership on laptops versus mobile devices.

Output the total viewership for laptop and mobile devices in the format of "laptop_views" and "mobile_views".

viewership Table:*/

--------Creating the table with sample data---------------

create table viewership

(

user_id  INT NOT NULL ,

device_type Varchar(10) NOT NULL ,

view_time DATETIME

)

select * from viewership 

insert into viewership values (123, 'tablet','01/02/2022 00:00:00')

insert into viewership values  (125,'tablet', '02/07/2022 00:00:00')

insert into viewership values  (127,'phone', '05/07/2022 00:00:00')

insert into viewership values  (128,'phone', '06/07/2022 00:00:00')

insert into viewership values  (129,'tablet', '07/07/2022 00:00:00')

insert into viewership values  (122,'phone', '08/07/2022 00:00:00')

insert into viewership values  (121,'laptop', '09/07/2022 00:00:00')

insert into viewership values  (132,'laptop', '11/07/2022 00:00:00')

insert into viewership values  (134,'laptop', '21/07/2022 00:00:00')

insert into viewership values  (155,'tablet', '18/07/2022 00:00:00')

SELECT A.[ Type of Devices],COUNT(1) 'Count of Devices'FROM

(

SELECT CASE WHEN device_type='tablet' THEN 'PhoneView' WHEN  device_type='phone' THEN 'PhoneView' ELSE 'LaptopView' END  as ' Type of Devices' from viewership 

) A

GROUP BY A.[ Type of Devices]

------------------END--------------------------------

Convert Comma Separated Values into Rows AIRNUB SQL INTREVIEW QUESTION

 ------------------------------script--------------

---------Find the room type that searched most no of times & output the room type with the no of searchs for it------

---------------------------------------------START----------------------------------

create table airbnb_searches 

(

user_id int,

date_searched date,

filter_room_types varchar(200)

);

delete from airbnb_searches;

insert into airbnb_searches values

(1,'2022-01-01','entire home,private room')

,(2,'2022-01-02','entire home,shared room')

,(3,'2022-01-02','private room,shared room')

,(4,'2022-01-03','private room')

-------TABLE CREATED WITH SAMPLE DATA-----------------------------

select A.value as "Room Type",count(A.value) as "No of times Search" from
(
select * from airbnb_searches Airnub
CROSS APPLY string_split (Airnub.filter_room_types,',')  
)A
GROUP BY A.value
ORDER BY count(A.value) DESC;

--------------------END-------------------------------------------------

Return all the employees those salary are same in same department #SQL INTREVIEW QUESTION

 ------------------------------------------SQL Interview Question --------------------------------

---Write the SQL to return all the employees those salary are same in same department-------------

------------------------------------------------------------Sample data & Code---------------------------------------------------

CREATE TABLE [emp_salary]

(

    [emp_id] INTEGER  NOT NULL,

    [name] NVARCHAR(20)  NOT NULL,

    [salary] NVARCHAR(30),

    [dept_id] INTEGER

);

INSERT INTO emp_salary

(emp_id, name, salary, dept_id)

VALUES(101, 'sohan', '3000', '11'),

(102, 'rohan', '4000', '12'),

(103, 'mohan', '5000', '13'),

(104, 'cat', '3000', '11'),

(105, 'suresh', '4000', '12'),

(109, 'mahesh', '7000', '12'),

(108, 'kamal', '8000', '11');

-------------------------------Table got created----------------------------------------------------------------------------------------------

SELECT emp_salary.name,  emp_salary.dept_id,emp_salary.emp_id,emp_salary.salary from (
select  (ES.dept_id),ES.salary FROM   emp_salary ES
INNER Join 
emp_salary ES_1 ON ES.dept_id=ES_1.dept_id AND ES.salary=ES_1.salary
group by ES.dept_id,ES.salary
Having count (1) > 1 
) Same_Sal_Dept
inner join emp_salary on emp_salary.dept_id=Same_Sal_Dept.dept_id AND
emp_salary.salary=Same_Sal_Dept.salary
ORDER BY emp_salary.dept_id DESC

REPEAT CUSTOMER & NEW CUSTOMER -SQL Interview Question -

D ay-2:

Question-2:

Date-5th Nov 2022

New and repeat customers Count from a customer table.

create table customer_orders (

order_id integer,

customer_id integer,

order_date date,

order_amount integer

);

select * from customer_orders

insert into customer_orders values(1,100,cast('2022-01-01' as date),2000),(2,200,cast('2022-01-01' as date),2500),(3,300,cast('2022-01-01' as date),2100)

,(4,100,cast('2022-01-02' as date),2000),(5,400,cast('2022-01-02' as date),2200),(6,500,cast('2022-01-02' as date),2700)

,(7,100,cast('2022-01-03' as date),3000),(8,400,cast('2022-01-03' as date),1000),(9,600,cast('2022-01-03' as date),3000)

select * from customer_orders

Select a.order_date,

Sum(Case when a.order_date = a.first_order_date then 1 else 0 end) as new_customer,

Sum(Case when a.order_date != a.first_order_date then 1 else 0 end) as repeat_customer

from(

Select customer_id, order_date, min(order_date) over(partition by customer_id) as first_order_date from customer_orders) a 

group by a.order_date;

Derive Points table for ICC tournament.-SQL IntreviewQuestion-1

 Please find the SQL Question & Answer.

Day-1:

Date-5th Nov 2022
Question-1:
Derive Points table for ICC tournament.

select ICC1.Team_1 "Team Name" FROM  icc_world_cup ICC1

UNION 

select ICC2.Team_2 FROM  icc_world_cup ICC2

INSERT INTO icc_world_cup values('Aus','India','India','NZ');

INSERT INTO icc_world_cup values('Aus','India','India','Aus');

INSERT INTO icc_world_cup values('Aus','India','India','SA');

INSERT INTO icc_world_cup values('Aus','India','India','SL');

ALTER TABLE icc_world_cup ADD DRAW varchar(100);

select * from  icc_world_cup ICC1 

select A.[Team Name],Count(1) as "No of Match Played",SUM (A.[Win])as "Winner",Count(1)-SUM (A.[Win]) as "Loss" from

(

select ICC1.Team_1 as "Team Name" ,CASE WHEN ICC1.Team_1=ICC1.Winner THEN 1 else 0 END as "Win"  from icc_world_cup ICC1

union all

select ICC1.Team_2,CASE WHEN ICC1.Team_2=ICC1.Winner THEN 1 else 0 END as "Win"  from icc_world_cup ICC1

)A

GROUP BY A.[Team Name]