WITH t as (SELECT t1.player_id as id, COUNT(t2.event_date) as count_consecutive_days
FROM activity as t1 LEFT JOIN activity as t2 
     ON date_add(t1.event_date, INTERVAL 1 DAY) = t2.event_date
GROUP BY t1.player_id)
SELECT ROUND(COUNT(count_consecutive_days)/COUNT(*), 2) as fraction
FROM t 
WHERE count_consecutive_days > 0

-- cumulative sum 
SELECT player_id,
       event_date,
       sum(games_played) over (partition by player_id order by event_date) as games_played_so_far
FROM activity
ORDER BY 1, 2;  

--??? mix two aggregates 
SELECT id, MAX(COUNT(DISTINCT friend)) as num_friends
FROM 
    (SELECT request_id as id, accepter_id as friend  
    FROM request_accepted
    UNION ALL 
    SELECT accepter_id as id, request_id as friend 
    FROM request_accepted) as t 
GROUP BY id 

SELECT d.dept_name, COUNT(s.student_id)
FROM department as d LEFT JOIN student as s 
     ON d.dept_id = s.dept_id 
GROUP BY d.dept_id
ORDER BY COUNT(s.student_id) DESC, d.dept_name; 

SELECT DISTINCT l1.id 
FROM logs l1 JOIN logs l2 
     ON l1.num = l2.num AND l2.id = l3.id - 1 
     JOIN logs l3 
     ON l2.num = l3.num AND l2.id = l3.id - 1; 

WITH tax_rates as (
    SELECT company_id, 
           CASE WHEN max_sal < 1000 THEN 0 
                ELIF max_sal >= 1000 AND max_sal <= 10000 THEN 0.24 
                ELSE 0.49
           END as rates     
    FROM (SELECT company_id, max(salary) as max_sal FROM salaries GROUP BY company_id) as t) 
SELECT s.company_id, s.employee_id, s.employee_name, (s.salary*(1-t.rates)) as salary 
FROM salaries as s JOIN tax_rates as t 
     ON s.company_id = t.company_id 

SELECT DISTINCT viewer_id as id 
FROM 
    (SELECT view_date, viewer_id 
    FROM views 
    GROUP BY view_date, viewer_id 
    HAVING COUNT(DISTINCT article_id) > 1) at t; 

SELECT COALESCE(a.sale_date, o.sale_date) as sale_date, 
       (IFNULL(a.sold_num, 0) - IFNULL(o.sold_num, 0)) as diff
FROM (SELCT * from sales where fruit = 'apples') as a 
     OUTTER JOIN 
     (SELECT * FROM sales WHERE fruit = 'orange') as o 
     ON a.sale_date = o.sale_date; 
ORDER BY 1; 

with t as (SELECT a.name as activity, COUNT(*) as counts 
FROM activities as a LEFT JOIN friends as f 
     ON a.name = f.activity 
GROUP BY a.name) 
SELECT activity
FROM t 
WHERE t.counts NOT IN (SELECT max(counts), min(counts) FROM t); 

SELECT DISTINCT business_id
FROM events as e INNER JOin (SELECT event_type, avg(occurances) as avg_occ 
                        FROM event_type 
                        GROUP BY event_type) as t 
     ON e.event_type = t.event_type 
WHERE e.occurances > t.avg_occ; 

WITH temp (SELECT DISTINCT t1.id  
FROM logins t1 JOIN logins t2 
     ON t2.login_date = DATE_ADD(t1.login_date, INTERVAL +1 DAY) AND t2.id = t1.id  
     JOIN logins t3 
     ON t3.login_date = DATE_ADD(t2.login_date, INTERVAL +1 DAY) AND t3.id = t2.id  
     JOIN logins t4 
     ON t4.login_date = DATE_ADD(t3.login_date, INTERVAL +1 DAY) AND t4.id = t3.id  
     JOIN logins t5 
     ON t5.login_date = DATE_ADD(t4.login_date, INTERVAL +1 DAY) AND t5.id = t4.id) 
SELECT t.id as id, a.name as name 
FROM accounts as a JOIN t 
     ON a.id = t.id
ORDER BY t.id; 

SELECT actor_id, director_id 
FROM actirdirector 
GROUP BY actor_id, director_id
HAVING COUNT(*) >= 3; 

SELECT p.product_id, ROUND(AVG(p.price*u.units), 2) as average_price 
FROM prices as p JOIN unitsold as u 
     ON p.purchase_date >= u.start_date AND p.purchase_date <= u.end_date
GROUP by p.product_id 

WITH t as (SELECT 
            CASE WHEN duration/60 >= 0 and duration/60 < 5 THEN '[0-5>'
                 ELIF duration/60 >= 5 AND duration/60 < 10 THEN '[5, 10>'
                 ELIF duration/60 >= 10 AND duration/60 < 15 THEN '[10, 15>'
                 ELSE '15 minutes or more'  
            END as bin 
           FROM sessions) 
SELECT bin, COUNT(*) as total 
FROM t 
GROUP BY bin; 

DELETE FROM person 
WHERE id NOT IN (SELECT MIN(id)
                 FROM person
                 GROUP BY email); 

SELECT email 
FROM person 
GROUP BY email 
HAVING COUNT(email) > 1; 

SELECT e.employee_id as employee_id, t.team_size as time_size 
FROM employee as e JOIN (SELECT team_id, COUNT(*) as team_size FROM employee GROUP BY                                team_id) as t
     ON e.team_id = t.team_id

SELECT DISTINCT c.title as title 
FROM TVProgram as t JOIN content as c 
     ON t.content_id = c.content_id 
WHERE t.program_date BEWTEEN ('2020-06-01', '2020-06-30')
      AND c.content_type = 'Movies'
      AND c.kids_content = 'Y'; 

SELECT sell_date, COUNT(DISTINCT product), GROUP_CONCAT(DISTINCT product) as products    
FROM activities 
GROUP BY sell_date;  

SELECT ROUND(COUNT(CASE WHEN order_date = customer_pref_delivery_date THEN 1 ELSE O                 END)/COUNT(*), 2) as immediate_percentage
FROM delivery 
GROUP BY delivery_id;  

SELECT p.product_name, o.unit
FROM orders as o JOIN products as p 
     ON o.product_id = p.product_id 
WHERE o.order_date BETWEEN ('2020-2-1', '2020-2-28') 
      AND o.unit >= 100;  

SELECT id, movie, description, rating   
FROM cinema
WHERE id%2 = 1  AND description <> 'boring'; 

SELECT s1.sub_id as post_id, COUNT(DISTINCT s2.parent_id) as num_of_comment  
FROM submission as s1 JOIN submission as s2 
     ON s1.sub_id = s2.parent_id 
GROUP BY s1.sub_id  

SELECT a.sub_id, COALESCE(COUNT(DISTINCT s.parent_id), 0) as number_of_comments 
FROM 
    (SELECT * 
    FROM submissions 
    WHERE parent_id IS NULL) as a 
    LEFT JOIN submissions as s
    ON a.sub_id = s.parent_id 
GROUP BY a.sub_id; 

SELECT p.product_name, s.year, s.price  
FROM sales as s JOIN product as p 
     ON s.product_id = p.product_id 

SELECT p.product_id, sum(s.quantity) as total_quantity 
FROM product as p LEFT JOIN sales as s
     ON p.product_id = s.product_id 
GROUP BY product_id

SELECT project_id
FROM ( 
    SELECT project_id, count(employee_id) as num_emp   
    FROM project 
    GROUP BY project_id
    ORDER BY count(employee_id) DESC) as t
WHERE 

-- PIVOT???? 
SELECT 
FROM

SELECT em.unique_id, e.name 
FROM employees as e LEFT JOIN employeeuni as em 
     ON e.id = em.id

SELECT extra as report_reason, count(distinct post_id) as count_post 
FROM actions 
WHERE action_date = '2019-07-04' AND action = 'report' 
GROUP BY extra

SELECT s.seller_id, SUM(p.unit*s.quantity) as total_sales 
FROM sales as s JOIN product as p 
     ON s.product_id = p.product_id 
GROUP BY s.seller_id
ORDER BY SUM(p.unit*s.quantity) DESC
LIMIT 1; 

--i just relize, no need join here, sum price and reorder it. 

--????? 
WITH t as (
    SELECT * 
    FROM sale as s inner join product as p 
         ON s.product_id = p.product_id
)

SELECT 
FROM t as t1 JOIN t as t2 
     ON t1.product_id = t2.production_id 
     AND t.production
     

SELET DISTINCT p.product_id, product_name 
FROM product as p INNER JOIN sales as s 
     ON p.product_id = s.product_id 
WHERE s.sale_date BETWEEN ('2019-01-01', '2019-03-31'); 

--??????????? 
SELECT s.student_id, s.student_name, e.subject_name, IFNULL(count(*), 0) as attended_exams
FROM (SELECT * FROM students CROSS JOIN subjects) as s 
     LEFT JOIN examinations as e  
     ON s.student_id = e.student_id AND s.subject_name = e.subject_name
GROUP BY s.student_id, e.subject_name;

SELECT c.country_name, 
       CASE WHEN AVG(weather_state) <= 15 THEN 'COLD' 
            ELIF AVG(weather_state) >= 25 THEN 'HOT'
            ELSE 'WARM'
       END as weather_type 
FROM weather as w JOIN countries as c 
     ON w.country_id = c.country_id 
WHERE YEAR(w.day) = 2019 AND MONTH(w.day) = 11 
GROUP BY c.country_name 

-- ??? 
SELECT employee_id, COUNT(team_id) over (PARTITION BY employee_id) as team_size 
FROM employee; 

--JOIN 
SELECT employee_id, t.team_size 
FROM employee JOIN 
     (SELECT team_id, COUNT(*) as team_size 
      FROM employee 
      GROUP BY team_id) as t 
     ON employee.team_id = t.team_id; 

SELECT ad_id, 
       ROUND(AVG(CASE WHEN action = 'Clicked' THEN 1 ELSE 0), 2) as CTR 
FROM ads 
GROUP BY ad_id
ORDER by 2 DESC, 1; 

-- left join
SELECT students.id, students.name
FROM students LEFT JOIN departments 
     ON students.department_id = departments.id
WHERE departments.id IS NULL;   

-- subquery 
SELECT id, name 
FROM students 
WHERE department_id NOT IN (SELECT id FROM departments); 

UPDATE 
SET sex = CASE WHEN 'm' THEN 'f'
          ELSE 'm'
          END; 

SELECT x, y, z
       CASE 
           WHEN x+y > z and x+z > y and z+y > x THEN 'YES'
           ELSE 'NO'
       END AS triangle
FROM triangle; 

SELECT users.name, IFNULL(sum(rides.distance), 0) AS distince_traveled,  
FROM users LEFT JOIN rides
     ON users.id = rides.user_id 
GROUP BY users.id
ORDER BY 2, 1;  

SELECT activity_data, COUNT(distinct user_id) 
FROM activity
WHERE activity BETWEEN (DATE_ADD('2019-07-27',INTERVAL -30 DAY), '2019-07-27')
GROUP BY activity_date 
HAVING COUNT(DISTINCT user_id) > 0; 

SELECT AVG(counts) as average_session_per_user FROM (
    SELECT COUNT(session_id) as counts, usr_id  
    FROM activity 
    WHERE activity_date between (DATE_ADD('2019-07-27', INTERVAL -30 DAY), '2019-07-27')
    GROUP BY user_id) AS a; 

SELECT p.firstname, p.lastname, a.city, s.state 
FROM person as p LEFT JOIN address as a 
     ON p.personid = a.personid; 

-- subquery
SELECT MAX(salary)
FROM employee 
WHERE salary < (SELECT MAX salary FROM employee);

-- use offset
SELECT DISTINCT salary 
FROM employee 
ORDER BY salary DESC
Limit 1 OFFSET 1; -- LIMIT n-1, OFFSET 1 (return the n highest salary) 

SELECT DISTINCT salary 
FROM employee 
ORDER BY salary DESC
LIMIT N-1 OFFSET 1; 

-- self join

-- rank function
WITH t as (
SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) ranks 
FROM score)
SELECT score, ranks -- rank is built-in function, so be careful
FROM score 

SELECT DISTINCT l1.num 
FROM logs as l1 join logs as l2 
     ON l1.id = l2.id - 1 AND l1.num = l2.num
     JOIN logs l3 
     ON l3.id -1 = l2.id AND l3.num = l2.num;  

-- self join
SELECT e1.name 
FROM employee as e1 join employee as e2 
     ON e1.managerid = e2.id
WHERE e1.salary > e2.salary; 

-- Non-equi join 
SELECT e1.name 
FROM employee as e1, 
     employee as e2
WHERE e1.managerid = e2.id 
      AND e1.salary > e2.salary; 

-- subquery 
SELECT email
FROM person
GROUP BY email 
HAVING COUNT(*) > 1; 
            
-- Non-equi self join
SELECT DISTINCT e1.email    
FROM person as p1 JOIN person as p2 
     ON p1.email = p2.email 
     AND p1.id <> p2.id; 

-- subquery 
SELECT Name
FROM customers
WHERE ID NOT IN (SELECT DISTINCT ID FROM orders); 

-- join 
SELECT customer.name
FROM customers LEFT JOIN orders 
     ON customers.id = orders.customerid 
groupy by customer.id 
having count(orders.id) = 0; 

-- subquery
SELECT name 
FROM employee 
WHERE salary in  
    (SELECT max(salary) 
    FROM employee
    GROUP BY departmentid); 
    
-- rank() function (boring tho lol)
WITH ranked_salary as (
    SELECT name,
           RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) as ranks 
    FROM employee)
SELECT NAME 
FROM ranked_salary
WHERE ranks = 1; 

WITH rank_table as (
SELECT e.name as name, DENSE_RANK() OVER (PARTITION BY e.DepartmentId ORDER BY e.salary DESC) as ranks
FROM employee as e JOIN department as d on e.DepartmentId = d.ID
)
SELECT name 
FROM rank_table 
WHERE ranks <= 3; 

-- ?? 
DELETE p1
FROM person as p1 JOIN person as p2 
     on p1.email = p2.email
WHERE p1.ID > p2.ID

-- self join
SELECT today.data 
FROM weather as today INNER JOIN weather as yesterday 
     ON DATEDIFF(today.date, yesterday.date) = 1 
WHERE today.temperature > yesterday.temperature; 

-- lag function
WITH t as (
    SELECT date,
           temperature as today_temp,
           LAG(temperature, 1) OVER (ORDER BY date) as yesterday_temp  
    FROM weather) 
SELECT date
FROM t 
WHERE today_temp > yesterday_temp; 

SELECT player_id, min(event_date)
FROM activity 
GROUP BY player_id 
ORDER BY 1; 

SELECT player_id, device_id
FROM 
    (SELECT player_id, device_id, min(event_date) as date 
    FROM activity 
    GROUP BY player_id
    ORDER BY min(event_date) ASC) as t; 

-- return the first login dates for each player 
SELECT ROUND(COUNT(t.event_date)/COUNT(*), 2) as fraction
FROM 
    (SELECT player_id, min(event_date) as event_date   
    FROM activity 
    group by player_id) as t 
    LEFT JOIN activity as a
    ON DATE_ADD(a.event_date, INTERVAL - 1) = t.event_date -- is ok??? 
GROUP BY player_id 

SELECT e.name as name, b.bonus as bonus
FROM employee as e LEFT JOIN bonus as b 
     ON e.empid = b.empid 
WHERE b.bonus < 1000 or b.bonus IS NULL; 

SELECT name
FROM customer 
WHERE referee_id != 2 or referee IS NULL; 

SELECT customer_name
FROM orders 
GROUP BY customer_number 
ORDER BY COUNT(order_number) 
LIMIT 1; 

SELECT name, population, area
FROM world 
WHERE area > 3,000,000 or population > 25,000,000; 

SELECT class
FROM classes 
GROUP BY class 
HAVING COUNT(DISTINCT student) >= 5;  

SELECT 
FROM 

SELECT DISTINCT c1.seat_id 
FROM cinema as c1 LEFT JOIN cinema as c2 
     ON c1.seat_id = c2.seat_id - 1
WHERE c1.free = 1 AND c2.free = 1
ORDER BY 1; 

SELECT name 
FROM salesperson 
WHERE sales_id NOT IN 
    (SELECT DISTINCT sales_id
    FROM orders as o 
         JOIN company as c 
         ON c.com_id = o.com_id 
    WHERE c.name = 'RED'); 

SELECT min(abs(abs(p1.x) - abs(p2.x))) as minimum  
FROM points as p1 INNER JOIN points as p2
     ON p1.x <> p2.x; -- since no duplicated values in x. 

SELECT MAX(num)
FROM 
    (SELECT num 
    FROM numbers
    GROUP BY num
    HAVING COUNT(num) = 1) as t;