SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users
FROM ga
WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY event_date_kst
-- HAVING event_date_kst>='2021-08-02' AND event_date_kst <= '2021-08-09'
ORDER BY dt ASC
SELECT event_date_kst AS dt, count(distinct user_pseudo_id) AS users
FROM ga
WHERE event_date_kst BETWEEN '2021-08-02' AND '2021-08-09'
GROUP BY event_date_kst
-- HAVING event_date_kst>='2021-08-02' AND event_date_kst <= '2021-08-09'
ORDER BY dt ASC
SELECT score
, dense_rank() OVER (ORDER BY score DESC) as 'rank'
FROM Scores
Q4. https://leetcode.com/problems/department-highest-salary/
SELECT d.name as Department
, e.name as Employee
, e.salary as Salary
FROM Employee e
INNER JOIN Department d on e.departmentId = d.id
WHERE e.salary=(SELECT max(salary)
FROM Employee e1
GROUP BY departmentId
HAVING departmentId=e.departmentId);
SELECT d.name as Department
, e.name as Employee
, e.salary as Salary
FROM Employee e
INNER JOIN Department d on e.departmentId = d.id
WHERE e.salary=(SELECT max(salary)
FROM Employee e1
GROUP BY departmentId
HAVING departmentId=e.departmentId);
--
WITH not_banned AS(
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")
)
SELECT request_at AS DAY
, ROUND(SUM(CASE WHEN status != 'completed' THEN 1 ELSE 0 END) / COUNT(*),2) AS 'Cancellation Rate'
FROM not_banned
GROUP BY request_at
WHERE request_at BETWEEN '2013-10-01' AND '2013-10-03'