Advanced SQL Sample

Use cases for SUBQUERY IN SELECT STATEMENT (Correlated Query), WITH, CASE, PIVOT

Scenario: There is a online coding contest that lasts for 15 days. The start date of the contest was March 01, 2016 and the end date was March 15, 2016.

Write a query to print total number of unique hackers who made at least submission each day (starting on the first day of the contest) — consecutive appearance, and find the hacker_id and name of the hacker who made maximum number of submissions each day. If more than one such hacker has a maximum number of submissions, print the lowest hacker_id. The query should print this information for each day of the contest, sorted by the date. — Source: HackerRank; Difficulty: Hard

WITH
submission_times_per_day_per_hacker AS
(
SELECT h.hacker_id,
h.name,
Count(h.hacker_id) AS ord,
submission_date as submission_date
FROM hackers h
INNER JOIN submissions s
ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id,
h.name,
s.submission_date
),
consecutive_hacker_list AS
(
SELECT DISTINCT s.submission_date,
s.hacker_id,
(SELECT Count(DISTINCT s2.submission_date)
FROM submissions s2
WHERE s2.hacker_id = s.hacker_id
AND s2.submission_date <=
s.submission_date
GROUP BY s2.hacker_id
HAVING Count(DISTINCT s2.submission_date) =
DATEDIFF(day, ‘2016–03–01’, s.submission_date) + 1) AS label_of_consec_days
FROM submissions s
WHERE s.submission_date between ‘2016–03–01’ and ‘2016–03–15’
),
consec_hacker_num AS
(
SELECT DISTINCT submission_date, COUNT(hacker_id) as num
FROM consecutive_hacker_list
WHERE label_of_consec_days IS NOT NULL
GROUP BY submission_date
)
SELECT DISTINCT st.submission_date,
ch.num,
FIRST_VALUE(st.hacker_id) OVER (PARTITION BY st.submission_date ORDER BY st.ord DESC, st.hacker_id),
FIRST_VALUE(st.name) OVER (PARTITION BY st.submission_date ORDER BY st.ord DESC, st.hacker_id)
FROM submission_times_per_day_per_hacker as st
INNER JOIN
consec_hacker_num as ch
ON st.submission_date = ch.submission_date

Scenario: For Uber services, please write a SQL query to find the cancellation rate of requests with unbanned users (both client and driver must not be banned) each day between "2013-10-01" and "2013-10-03".

The cancellation rate is computed by dividing the number of canceled (by client or driver) requests with unbanned users by the total number of requests with unbanned users on that day.

Return the result table in any order. Round Cancellation Rate to two decimal points. — Source: Leetcode; Difficulty: Hard

#Use (case when … then 1 else null end) to count the repetition times of specific value
SELECT Request_at as Day, ROUND((COUNT(CASE WHEN status <> ‘completed’ THEN 1 ELSE NULL END)/COUNT(status)),2) as ‘Cancellation_Rate’
#Create a intermediate table for Two Columns Value Checking
FROM (SELECT * FROM Trips
WHERE Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = ‘Yes’)
AND Driver_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = ‘Yes’)) AS Tr
WHERE Request_at BETWEEN ‘2013–10–01’ AND ‘2013–10–03’
GROUP BY Request_at

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation

SELECT <non-pivoted column>,
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
…
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
IN ( [first pivoted column], [second pivoted column],
… [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

SELECT unpivoted_columns,[Doctor],[Professor],[Singer],[Actor]
FROM (
select row_number() over (partition by OCCUPATION order by name) as unpivoted_columns, NAME, OCCUPATION FROM OCCUPATIONS
) as sourceTable
PIVOT
(
MAX(NAME)
FOR OCCUPATION IN ([Doctor],[Professor],[Singer],[Actor])
) as pivotTable

Method 2:

select Doctor, Professor, Singer, Actor
from (
select
NameOrder
,max(case Occupation when 'Doctor' then Name else NULL end) as Doctor
,max(case Occupation when 'Professor' then Name else NULL end) as Professor
,max(case Occupation when 'Singer' then Name else NULL end) as Singer
,max(case Occupation when 'Actor' then Name else NULL end) as Actor
from (
select
Occupation
,Name
,row_number() over(partition by Occupation order by Name ASC) as NameOrder
from Occupations
) as NameLists
group by NameOrder
) as Names