- Highest Cost Orders.sql
- Premium vs Freemium.sql
- 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
-- 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 | |
; |
-- 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 | |
; |
-- 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 | |
; |
-- 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 | |
; |
-- 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 | |
; |
-- 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 |
-- 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 | |
; |
-- 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 |