Emp Id Full Name Manager ID Date of joining Emp Id
2320 Manpreet Singh 1114 01/31/2019 2320
1596 Rahul Gupta 1234 01/30/2020 1596
3497 Simran Sharma 889 27/11/2023 6987
Employees Salaries
Q1 All employees whose salaries are between 20000 and 50000
select [Link], E."Full Name", [Link] from Employees E join Salaries S on [Link] Id = S.
Q2 All employees who joined in the Year 2023
Q3 All employees who are not working on any project
Select [Link], [Link] from Employees E left join Salaries S where [Link] is NULL
Q4 Project wise count of employees
Select distinct Project, COUNT() OVER(PARTITION BY Project) as count_proj;
Q5 Count of all the employees manager wise and their total salary
Q6 List down all employees whose names begin with S
Project Salary
P0 11000
P2 13000
P1 45000
ries S on [Link] Id = S. Emp ID where [Link] between 20000 and 50000
re [Link] is NULL
count_proj;
Question 1
what is the order of execution of below clauses/statements when a SQL query is executed
Sno. Order of execution
1 Group BY FROM
2 JOIN JOIN
3 SELECT WHERE
4 LIMIT Group BY
5 FROM HAVING
6 ORDER BY SELECT
7 HAVING ORDER BY
8 WHERE LIMIT
Question 2
ColA ColB ColA
x y x x
x z x x
x a x x
x b x x
x x x
x x x
x x
inner join 8 x x
left 4
right 2
full outer 8/4
Question 3
This is a table "family". Write a query that gives grandfather and grandson combination in the output
family
Son Father select [Link] as grandfather, [Link] as grandson
a b from family F1, family F2
b c where [Link] = [Link]
d e
e g
f g
Questions 4
There is a table "Team". Each team will have a match with everyother team. I need all possible combinations. Outp
Team Required Output with cte as (select [Link] as t1, t2
RCB t1 t2 from Teams T1 cross join Teams T2
MI RCB MI
CSK RCB CSK , cte2 as (select * from cte
KKR RCB KKR where t1!=t2
MI CSK
MI KKR
CSK KKR select
Question
We want to5 identify the most suspicious claims in each state.
We'll consider the top 5 percentile of claims with the highest fraud scores in each state as potentially fraudulent.
fraud_score
policy_numberclaim_cost fraud_score state
1 120 97 Delhi
2 14 56 Delhi
3 156 87 Delhi
4 1566 90 Mumbai
5 120 32 Mumbai
6 120 0 Mumbai
CASE STUDY
1. Growth/degrowth in number of orders being placed
2 Number of gold members sign ups - trends in those 3 months
3 AOV - KPI for gold memebers - has a persons AOV increased since they became a gold members //
4 CLV -
if num orders = INR 100 then breakeven --
x x x x
x x x x
x x x x
x x x x
x x x x
x x x x
x x x x
x x x x
y NULL y NULL
x - z NULL
x - a NULL
x b NULL
ation in the output
[Link] as grandson
d all possible combinations. Output is given for reference
cte2
h cte as (select [Link] as t1, [Link] as t2 select [Link], [Link]
m Teams T1 cross join Teams T2) from Team T1, Teams T2
Where [Link] != [Link]
e2 as (select * from cte
and TO_CHAR([Link]) < [Link]::STRING
state as potentially fraudulent.
e they became a gold members // AOV og gold members vs non gold
x x INNER JOIN + values only in left + only in right
x x
x x
x x
x x
x x
x x
x x
Left ones already included
NULL y Only Right will come
NULL z
NULL a
NULL b
team1 team2 CONCAT COUNT SELECT team1, team2 from
RCB MI RCBMI 2 (SELECT team1,team2,CONCAT, RANK()
RCB CSK RCBCSK 2 (select CASE WHEN team1>team2 then te
RCB KKR WHEN team1<team2 then team2||team1
MI RCB RCBMI 2 END as CONCAT)v1)v2
MI CSK where rank = 1;
MI KKR
team2 from
team2,CONCAT, RANK() OVER(PARTITION BY CONCAT)
HEN team1>team2 then team1||team2
am2 then team2||team1
res_id dish_name item_price
1 aloo paratha 99
1 pyaaz paratha 20
1 lassi 20
1 chilli potato 150
1 burger 120
2 fries 120
2 cheesy fries 140
1 salsa dip 20
3 cheese pizza 150
3 mushroom pizza 160
dishes
Question Find the 3 least expensive dishes for each restaurant (sample dataset above) & overall platform
e dataset above) & overall platform
res_id date of order orders
1 2023-01-01 25
1 2023-01-02 40
1 2023-01-04 51
1 2023-01-05 81
1 2023-01-07 107
1 2023-01-12 152
orders
Question Find the maximum consecutive number of days for which restaurant received an order
staurant received an order
User Table Gold Table
user_id date orders user_id
1 2023-01-01 1 1
1 2023-01-02 1 2
1 2023-01-03 3 3
1 2023-01-04 2 4
2 2023-01-05 0 5
3 2023-01-06 1 6
4 2023-01-07 2 7
5 2023-01-08 1 8
5 2023-01-09 2 9
5 2023-01-10 1 10
gold_buy_date
2023-01-01
2023-03-30
2023-04-25
2023-07-21
2023-07-22
2023-08-10
2023-07-10
2023-06-14
2023-04-08
2023-03-26
You have 25 horses, you want to pick the fastest 3 horses out of those 25. In each race, only 5 horses can run at th
only 5 horses can run at the same time. What is the minimum number of races required to find the 3 fastest horses without usin
astest horses without using a stopwatch?
Two jugs have 3 liters and 5 liters of water capacity. You are tasked with filling up a bucket w
with filling up a bucket with 4 liters of water. You must use the jugs to measure the water in the bucket.
Question - No. of tyres sold in an year
city_name item_count item_ov item_gmv city_ov city_gmv platform_ov
Delhi NCR 145,384 600,402 ### 18,322,648 ### ###
Delhi NCR 36,410 116,620 26,623,154 18,322,648 ### ###
Delhi NCR 126,310 384,998 98,244,650 18,322,648 ### ###
Delhi NCR 96,097 436,678 92,652,696 18,322,648 ### ###
Pune 65,393 223,653 55,227,310 5,877,550 ### ###
Pune 68,423 355,341 82,230,210 5,877,550 ### ###
Pune 56,073 264,901 58,289,420 5,877,550 ### ###
Pune 16,135 73,588 15,236,500 5,877,550 ### ###
Pune 7,072 42,976 9,208,900 5,877,550 ### ###
Jaipur 24,978 101,582 22,207,300 2,044,878 ### ###
Jaipur 32,758 173,773 41,899,260 2,044,878 ### ###
Jaipur 23,994 108,438 25,786,858 2,044,878 ### ###
Jaipur 15,247 43,291 11,302,045 2,044,878 ### ###
Jaipur 14,375 56,784 11,761,287 2,044,878 ### ###
#NAME?
platform_gmv
###
### 0.0%
###
###
###
###
###
###
###
###
###
###
###
###