25 SQL Practice Problems With Solutions:: Exercises-5fc791e24082
25 SQL Practice Problems With Solutions:: Exercises-5fc791e24082
25 SQL Practice Problems With Solutions:: Exercises-5fc791e24082
https://towardsdatascience.com/twenty-five-sql-practice-
exercises-5fc791e24082
1. Cancellation rates
From the following table of user IDs, actions, and dates, write a
query to return the publication and cancellation rate for each user.
with lastDate as
(
select user_id, unix_timestamp("action_date") as latestDate from lastAction
)
with secondLast as
(
select user_id, unix_timestamp("actionDate") as secondDate from lastAction
)
with finalResult(
select user_id,((lastDate-secondLast)/86400) as daysElapse
from lastdate l
left join secondLast s where l.user_id=s.user_id
5. Super users
A company defines its super users as those who have made at least
two transactions. From the following table, write a query to return,
for each user, the date when they become a super user, ordered by
oldest super users first. Users who are not super users should also
be present in the table.
t1 as
(
sum(case when a1.mbileUser is not null then 1 else 0) as mobileCount,
sum(case when a1.webUser is not null then 1 else 0) as webCount,
sum(case when a1.mbileUser is not null and a1.webUser is not null then 1
else 0) as bothCount,
count(*) from a1 as total
a2 as
(
select
sum(case when ((a1.accessDate-a1.joinDate)<86400*30) then 1 else 0) as
upgradeCount
from a1
)
select a2.upgradeCount/a1.totalCount
9. Most friended
Given the following table, return a list of users and their
corresponding friend count. Order the result by descending friend
count, and in the case of a tie, by ascending user ID. Assume that
only unique friendships are displayed
(i.e., [1, 2] will not show up again as [2, 1] ). From LeetCode.
Write a query to return the start and end dates of each project, and
the number of days it took to complete. Order by ascending project
duration, and descending start date in the case of a tie.
From HackerRank.
WITH projects (task_id, start_date, end_date)
AS (VALUES
(1, CAST('10-01-20' AS date), CAST('10-02-20' AS date)),
(2, CAST('10-02-20' AS date), CAST('10-03-20' AS date)),
(3, CAST('10-03-20' AS date), CAST('10-04-20' AS date)),
(4, CAST('10-13-20' AS date), CAST('10-14-20' AS date)),
(5, CAST('10-14-20' AS date), CAST('10-15-20' AS date)),
(6, CAST('10-28-20' AS date), CAST('10-29-20' AS date)),
(7, CAST('10-30-20' AS date), CAST('10-31-20' AS date))),
-- get start dates not present in end date column (these are “true”
project start dates) t1 AS (
SELECT start_date
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects) ),-- get
end dates not present in start date column (these are “true”
project end dates) t2 AS (
SELECT end_date
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects) ),-- filter
to plausible start-end pairs (start < end), then find correct end
date for each start date (the minimum end date, since there are no
overlapping projects)t3 AS (
SELECT start_date, min(end_date) AS end_date
FROM t1, t2
WHERE start_date < end_date
GROUP BY start_date )SELECT *, end_date - start_date AS
project_duration
FROM t3
ORDER BY project_duration ASC, start_date ASC
10. a1 as
(
select unix_timestamp(start_date),unix_timestamp(end_date) from projects
where start_date not in end_date and
end_date not in start_date
order by start_date,end_date
)
a2 as
(
select a1.start_date,a1.end_start,(a1.end_date-a1.start_date)/86400 as
projectDays
from a1
)
)
select count(*) as totalCount from students
a2 as
(
select count(a1.*)/totalCount as fractionValue from a1,students s
where a1.student_id=s.student_id
)
a2 as
(
select a1.hacker_id,sum(a1.score) as maxScore from a1
group by a1.hacker_id
a3 as
(
select a2.hacker_id,h.name, a1.maxScore from a2
left join hackers h on h.hackers_id=a2.hackers_id
group by a2.hacker_id,h.name
order by maxScore desc,hacker_id
)