Hard

Twitter
ID 9897
Highest Salary In Department
Find the employee with the highest salary per department. Output the department
name, employee's first name along with the corresponding salary.

Soln1:

SELECT

department AS department, first_name AS employee_name , salary

FROM employee

WHERE (department , salary) IN

(SELECT department, MAX(salary)

FROM employee

GROUP BY department);

Soln2:

SELECT tem.department, tem.first_name, tem.salary FROM (

SELECT department, first_name, salary,

rank() OVER (partition by department order by salary desc) as Rank

FROM employee) tem

WHERE rank = 1

Soln3:

with CTE as (SELECT

dense_rank() over (partition by department order by salary desc) as r

from employee)

select first_name, department, salary from CTE where r=1

Hard
Amazon
ID 9915
Highest Cost Orders
Find the customer with the highest order cost from 2019-02-1 to 2019-05-1. Output the
first name along with the order cost and the date.

- Use an INNER JOIN on customer id to combine both tables.

- Use the WHERE clause set conditions regarding the period and the highest order cost.

- Use an inner query under WHERE clause conditions to find the highest order cost within the given
range of dates.

- Use the MAX() function to find the highest order cost and BETWEEN to set the range of dates.
Soln1:

SELECT c.first_name, o.order_cost, o.order_date from orders o

INNER JOIN customers c ON o.cust_id = c.id

WHERE o.order_date between '2019-02-1' and '2019-05-1'

ORDER BY 2 desc

LIMIT 1

Soln2:

SELECT customers.first_name, order_cost, order_date

FROM orders

INNER JOIN customers ON orders.cust_id = customers.id

WHERE order_date BETWEEN '2019-02-1' AND '2019-05-1'

AND order_cost IN (SELECT MAX(order_cost) FROM orders WHERE order_date BETWEEN '2019-02-1'
AND '2019-05-1' )

Hard
Microsoft
ID 10300
Premium vs Freemium
Find the total number of downloads for paying and non-paying users by date. Include
only records where non-paying customers have more downloads than paying
customers. The output should be sorted by earliest date first and contain 3 columns
date, non-paying downloads, paying downloads.
- ms_user_dimension will help link the number of downloads with whether an account is paying or non-
paying

- Join ms_user_dimension, ms_acc_dimension, ms_download facts using LEFT JOIN

- Use a CASE WHEN to help sum the total number of downloads between paying and non-paying users

- Sort output by date in ascending order.

Soln1:

SELECT date, non_paying, paying

FROM (SELECT date, sum(CASE

WHEN paying_customer = 'yes' THEN downloads

END) AS paying,

sum(CASE

WHEN paying_customer = 'no' THEN downloads

END) AS non_paying

FROM ms_user_dimension a

LEFT JOIN ms_acc_dimension b ON a.acc_id = b.acc_id

LEFT JOIN ms_download_facts c ON a.user_id=c.user_id

GROUP BY date

ORDER BY date) t

GROUP BY t.date, t.paying, t.non_paying

HAVING (non_paying - paying) >0

ORDER BY t.date ASC

Soln2:

WITH tmp AS(

SELECT a.user_id, b.paying_customer

FROM ms_user_dimension a

JOIN ms_acc_dimension b

ON a.acc_id = b.acc_id

),
tmp2 AS(

SELECT c.*, d.paying_customer

FROM ms_download_facts c

JOIN tmp d

ON c.user_id = d.user_id

SELECT date,

SUM(CASE WHEN paying_customer = 'yes' THEN downloads ELSE 0 end) as paying,

SUM(CASE WHEN paying_customer = 'no' THEN downloads ELSE 0 end) as non_paying

FROM tmp2

GROUP BY 1

HAVING SUM(CASE WHEN paying_customer = 'yes' THEN downloads ELSE 0 end) < SUM(CASE WHEN
paying_customer = 'no' THEN downloads ELSE 0 end)

ORDER BY 1

Hard
Amazon
ID 10142
No Order Customers
Find customers who didn't place orders from 2019-02-01 to 2019-03-01. Output
customer's first name.
Soln1:

select first_name from customers

WHERE id not in (

select cust_id from orders

where order_date between '2019-02-01' and '2019-03-01')

Hard
Yelp
ID 10060
Top Cool Votes
Find the business and the review_text that received the highest number of 'cool' votes.
Output the business name along with the review text.

Soln1:

with tem as (select business_name, review_text, sum(cool) as sum_cool from yelp_reviews

GROUP BY business_name, review_text

order by 3 desc)

SELECT business_name, review_text from tem

WHERE sum_cool = (SELECT max(sum_cool) from tem)

Soln2:

SELECT

business_name, review_text
FROM yelp_reviews reviews

INNER JOIN

(SELECT max(cool) AS max_cool FROM yelp_reviews

) mc

ON reviews.cool = mc.max_cool

Hard
Airbnb
ID 9632
Host Popularity Rental Prices
--You’re given a table of rental property searches by users. Find the minimum, average,
maximum rental prices for each host’s popularity rating. The host’s popularity rating is
defined as below:
 0 reviews: New
 1 to 5 reviews: Rising
 6 to 15 reviews: Trending Up
 16 to 40 reviews: Popular
 more than 40 reviews: Hot
Soln1:

WITH tem1 AS (

select id, price,

CASE WHEN number_of_reviews = 0 THEN 'New'

WHEN number_of_reviews between 1 and 5 THEN 'Rising'

WHEN number_of_reviews between 6 and 15 THEN 'Trending Up'

WHEN number_of_reviews between 16 and 40 THEN 'Popular'

ELSE 'Hot'

END as popularity_rating

from airbnb_search_details

SELECT popularity_rating, min(price), avg(price), max(price) from tem1

GROUP BY popularity_rating