SQL Exercises - StrataScratch

.SQL Exercises - StrataScratch.md

SQL Exercises

Difficulty

Hard

• Highest Cost Orders.sql
• Premium vs Freemium.sql

Medium

• Acceptance Rate By Date.sql
• Customer Details.sql
• Finding Updated Records.sql
• Popularity of Hack.sql
• Revenue Over Time.sql
• Users By Avg Session time.sql

Acceptance Rate By Date.sql

-- https://platform.stratascratch.com/coding/10285-acceptance-rate-by-date

with accepted as (
    select sentTbl.date, count(*) count_accept
    from fb_friend_requests sentTbl
    inner join fb_friend_requests acceptTbl
    on sentTbl.user_id_sender = acceptTbl.user_id_sender
    and sentTbl.user_id_receiver = acceptTbl.user_id_receiver
    and sentTbl.action = 'sent'
    and acceptTbl.action = 'accepted'
    group by 1)
, sent as (
    select date, count(*) count_sent
    from fb_friend_requests 
    where action = 'sent' 
    group by date)

select
    sent.date
    , accepted.count_accept::float/sent.count_sent as percentage_acceptance
from sent
inner join accepted
on sent.date = accepted.date
;

Customer Details.sql

-- https://platform.stratascratch.com/coding/9891-customer-details

select
    customers.first_name
    , customers.last_name
    , customers.city
    , orders.order_details
from customers
left join orders
on customers.id = orders.cust_id
order by customers.first_name asc, orders.order_details asc
;

Finding Updated Records.sql

-- https://platform.stratascratch.com/coding/10299-finding-updated-records

with currentSalary as (
    select
        id
        , max(salary) maxSalary
    from ms_employee_salary
    group by id
)

select distinct
    currentSalary.id "Employee ID"
    , baseTbl.first_name "Employee First Name"
    , baseTbl.last_name "Employee Last Name"
    , baseTbl.department_id "Employee Department ID"
    , currentSalary.maxSalary "Employee Current Salary"
from currentSalary
inner join ms_employee_salary baseTbl
on currentSalary.id = baseTbl.id
order by currentSalary.id asc
;

Highest Cost Orders.sql

-- https://platform.stratascratch.com/coding/9915-highest-cost-orders

with total_order_cost_daily as (
select 
    order_date
    , first_name
    , total_order_cost total
    , dense_rank() over(order by total_order_cost desc) order_rank
from customers cust
left join orders on cust.id = orders.cust_id
where order_date between '2019-02-01' and '2019-05-01'
)

select
    first_name "First Name"
    , total "Total Cost"
    , order_date "Order Date"
from total_order_cost_daily
where order_rank = 1
;

Popularity of Hack.sql

-- https://platform.stratascratch.com/coding/10061-popularity-of-hack

select 
    emp.location "Location"
    , avg(survey.popularity) "Average Popularity"
from facebook_employees emp
left join facebook_hack_survey survey
on emp.id = survey.employee_id
group by emp.location
order by avg(survey.popularity) desc
;

Premium vs Freemium.sql

-- https://platform.stratascratch.com/coding/10300-premium-vs-freemium

with lookup as (
select 
    date
    , sum(case when paying_customer = 'yes' then downloads end) as paying
    , sum(case when paying_customer = 'no' then downloads end) as free
from ms_user_dimension users
left join ms_acc_dimension accs
on users.acc_id = accs.acc_id
left join ms_download_facts facts
on users.user_id = facts.user_id
group by facts.date
order by facts.date asc
)

select
    *
from lookup
where free > paying
;

Revenue Over Time.sql

-- https://platform.stratascratch.com/coding/10314-revenue-over-time

with sales_avg_mth as (
    SELECT 
        to_char(created_at::date, 'YYYY-MM') date
        , SUM(purchase_amt) amt
    FROM amazon_purchases
    GROUP BY date
)

SELECT
    sales_avg_mth.date "Year-Month"
    , AVG(sales_avg_mth.amt)
        OVER(ORDER BY sales_avg_mth.date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) 
        "Rolling Monthly Average Amount"
FROM sales_avg_mth

Top Businesses With Most Reviews.sql

-- https://platform.stratascratch.com/coding/10048-top-businesses-with-most-reviews

select 
    name "Business Name"
    , review_count as "Total Number of Reviews"
from yelp_business
order by review_count desc
limit 5
;

Users By Avg Session time.sql

-- https://platform.stratascratch.com/coding/10352-users-by-avg-session-time

with userPageLoad as (
    select
        user_id as idUserPageLoad
        , timestamp::date as dateUserPageLoad
        , max(timestamp) as timeUserPageLoadLast 
    from facebook_web_log
    where action = 'page_load'
    group by user_id, timestamp::date
)
, userPageExit as (
    select
        user_id as idUserPageExit
        , timestamp::date as dateUserPageExit
        , min(timestamp) as timeUserPageLoadFirst 
    from facebook_web_log
    where action = 'page_exit'
    group by user_id, timestamp::date
)

select
    idUserPageLoad as user_id
    , AVG(upx.timeUserPageLoadFirst - upl.timeUserPageLoadLast) as avg
from userPageLoad upl
inner join userPageExit upx
on upl.idUserPageLoad = upx.idUserPageExit
and upl.dateUserPageLoad = upx.dateUserPageExit
group by upl.idUserPageLoad