1.0_leetcode_database_practice

*175.

SELECT Person.FirstName, Person.LastName, Address.City, Address.State
FROM Person
LEFT JOIN Address
ON Person.PersonID = Address.PersonID

176.

SELECT (
  SELECT DISTINCT Salary FROM Employee 
  ORDER BY Salary DESC
  LIMIT 1,1
)as SecondHighestSalary 

notes:

• limit y 分句表示: 读取 y 条数据
• limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
• limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据

177.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
  RETURN (
      SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1
  );
END

NOTES: MUST use declare and set in this case

178. MySQL Solution:

SELECT S.Score, COUNT(S2.Score) AS Rank FROM Scores S,
(SELECT DISTINCT Score FROM Scores) S2
WHERE S.Score<=S2.Score
GROUP BY S.Id 
ORDER BY S.Score DESC;

MS SQL Solution

SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Scores

180. Assume the id is consecutive: 1,2,3

SELECT DISTINCT l1.Num AS ConsecutiveNums
FROM Logs l1, Logs l2,Logs l3
WHERE l1.Id + 1 = l2.Id AND l2.Id + 1 = l3.Id
AND l1.Num = l2.Num AND l2.Num = l3.Num

select distinct Num as ConsecutiveNums
from Logs
where (Id + 1, Num) in (select * from Logs) and (Id + 2, Num) in (select * from Logs)

181.

SELECT a.Name AS Employee 
FROM Employee a
JOIN Employee b
ON a.ManagerId = b.Id
AND a.Salary > b.Salary

182.

SELECT Email
FROM Person
GROUP BY Email
Having count(Email) > 1

183.

SELECT Customers.Name AS Customers
FROM Customers 
WHERE Customers.id NOT IN 
(SELECT CustomerId FROM Orders)

184.

SELECT d.Name AS 'Department', e.Name AS 'Employee',e.Salary FROM Employee e
JOIN Department d
ON e.DepartmentId = d.Id
WHERE
 (DepartmentId,Salary) IN (
     SELECT DepartmentId, Max(Salary)
     FROM Employee
     GROUP BY DepartmentId
 )

185.

SELECT d.Name AS 'Department', e1.Name AS 'Employee',e1.Salary FROM Employee e1
JOIN Department d
ON e1.DepartmentId = d.Id
WHERE 3 > (
 SELECT COUNT(DISTINCT e2.Salary)
 FROM Employee e2
 WHERE e2.Salary > e1.Salary AND e1.DepartmentId = e2.DepartmentId
)

SELECT d.Name AS Department, a. Name as Employee, a. Salary 
FROM (
SELECT e.*, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS DeptPayRank 
FROM Employee e 
) a 
JOIN Department d
ON a. DepartmentId = d. Id 
WHERE DeptPayRank <=3

196.

DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id

197.

SELECT w1.Id
FROM weather w0, weather w1
WHERE DATEDIFF(w1.recorddate, w0.recorddate) = 1 and w0.temperature < w1.temperature

262.

SELECT Request_at as Day,
       ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
      AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;

511.

SELECT player_id,MIN(event_date)AS first_login
FROM Activity
GROUP BY player_id

512.

select player_id, device_id 
from activity 
where (player_id, event_date) in (
                                select player_id, min(event_date)
                                from activity 
                                group by player_id
                                 ) 

534.

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 player_id, games_played_so_far;

SELECT a1.player_id, a1.event_date,
SUM(a2.games_played) AS games_played_so_far
FROM activity a1, activity a2
WHERE a1.player_id = a2.player_id
AND a1.event_date >=a2.event_date
GROUP BY a1.player_id, a1.event_date
ORDER BY a1.player_id, a1.event_date;

550.

SELECT ROUND(COUNT(t2.player_id)/COUNT(t1.player_id),2) AS fraction
FROM (
    SELECT player_id, MIN(event_date) AS first_login FROM Activity GROUP BY player_id
) t1
LEFT JOIN Activity t2
ON t1.player_id = t2.player_id AND t1.first_login = t2.event_date - 1

569.

SELECT MIN(A.Id) AS Id, A.Company, A.Salary
FROM Employee A, Employee B
WHERE A.Company = B.Company
GROUP BY A.Company, A.Salary
HAVING SUM(CASE WHEN B.Salary >= A.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2
AND SUM(CASE WHEN B.Salary <= A.Salary THEN 1 ELSE 0 END) >= COUNT(*)/2

570.

SELECT Name FROM Employee
WHERE Id IN 
(SELECT ManagerId FROM Employee GROUP BY ManagerId HAVING COUNT(ManagerId) >=5)

571.(HARD)

SELECT AVG(n1.Number) AS median
FROM Numbers n1
JOIN (
    SELECT n3.Number,
    ABS(SUM(CASE WHEN n3.Number > n4.Number THEN n4.Frequency ELSE 0 END) - 
        SUM(CASE WHEN n3.Number < n4.Number THEN n4.Frequency ELSE 0 END))
    AS Diff
    FROM Numbers n3, Numbers n4 #为什么inner join不对？
    GROUP BY n3.Number
     ) n2
ON n1.Number =n2.Number
WHERE n1.Frequency >= n2.Diff

LOGIC:比它大的数的频数和与比它小的数的频数和的差需要落在中位数的频数里 NUM, FREQ, DIFF 0,7,7 1,1,3 2,3,7 3,11

574.

SELECT Name FROM Candidate
WHERE id = (
SELECT CandidateId FROM Vote 
GROUP BY CandidateID
ORDER BY COUNT(id) DESC
LIMIT 1
)

*577.

SELECT e.name, b.bonus 
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE b.bonus < 1000 OR b.bonus IS NULL

578.

SELECT question_id AS survey_log
FROM(
    SELECT question_id,
    SUM(CASE WHEN action = 'answer' THEN 1 ELSE 0 END)
    /
    SUM(CASE WHEN action = 'show' THEN 1 ELSE 0 END) AS rate
    FROM survey_log
    GROUP BY question_id
    ) tem
ORDER BY rate DESC 
LIMIT 1

579.

SELECT e1.ID,e1.Month, Sum(e2.Salary) As Salary FROM Employee e1
JOIN Employee e2
ON e1.ID = e2.ID and e1.Month >= e2.Month
WHERE(e1.ID, e1.Month) NOT IN (SELECT Id, MAX(Month) FROM Employee GROUP BY Id)
      AND e1.Month - e2.Month < 3
GROUP BY e1.Id, e1.Month
ORDER BY e1.ID, e1.Month DESC

SELECT Id, Month, SUM(Salary) OVER(PARTITION BY Id ORDER BY Month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS Salary
FROM 
(SELECT Id, Month, Salary, MAX(Month) OVER(PARTITION BY Id) AS recent_month
FROM Employee) sub
WHERE sub.Month != recent_month
ORDER BY Id, Month DESC

reference for windows function: https://blog.csdn.net/huozhicheng/article/details/5843782

580.

SELECT d.dept_name, COUNT(student_id) AS student_number FROM
student s
RIGHT JOIN department d
ON d.dept_id = s.dept_id
GROUP BY d.dept_id
ORDER BY student_number DESC,d.dept_name

*584.

SELECT name FROM customer
WHERE referee_id != 2 OR referee_id IS NULL

585.

SELECT SUM(TIV_2016) AS TIV_2016
FROM insurance
WHERE TIV_2015 IN (
    SELECT TIV_2015 FROM insurance
    GROUP BY TIV_2015
    HAVING COUNT(*) > 1
) AND CONCAT(LAT,CONCAT(,LON)) IN(
    SELECT CONCATE(LAT,CONCATE(,LON)) FROM insurance
    GROUP BY LAT,LON
    HAVING COUNT(*) = 1
)

586.

SELECT customer_number
FROM orders
GROUP BY customer_number
ORDER BY COUNT(*) DESC
LIMIT 1

*595

SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000 OR population > 25000000

*596

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

597

SELECT 
ROUND(
    IFNULL(
    (SELECT COUNT(distinct requester_id, accepter_id) FROM request_accepted)
    /
    (SELECT COUNT(distinct sender_id, send_to_id) FROM friend_request),
    0)
, 2) AS accept_rate

601

SELECT DISTINCT S1.*
FROM stadium S1
JOIN stadium S2
JOIN stadium S3
ON ((S1.id = S2.id - 1 AND S1.id = S3.id -2) # s1,s2,s3 
OR (S3.id = S1.id - 1 AND S3.id = S2.id -2)  # s3,s1,s2
OR (S3.id = S2.id - 1 AND S3.id = S1.id -2)) # s3,s2,s1
WHERE S1.people >= 100
AND S2.people >= 100
AND S3.people >= 100
ORDER BY S1.id;

Notes:Considering t1, t2 and t3 are identical, we can take one of them to consider what conditions we should add to filter the data and get the final result. Taking t1 for example, it could exist in the beginning of the consecutive 3 days, or the middle, or the last.

603.

SELECT DISTINCT a.seat_id
FROM cinema a
JOIN cinema b 
ON 
ABS(a.seat_id - b.seat_id) = 1
WHERE a.free = 1 AND b.free = 1
ORDER BY a.seat_id;

607

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

610

SELECT 
    x,
    y,
    z,
    CASE
        WHEN x + y > z AND x + z > y AND y + z > x THEN 'Yes'
        ELSE 'No'
    END AS 'triangle'
FROM
    triangle

613.

SELECT
    MIN(ABS(p1.x - p2.x)) AS shortest
FROM point p1
JOIN point p2 
ON p1.x != p2.x

619

SELECT IFNULL(
    (SELECT num
     FROM my_numbers
     GROUP BY 1
     HAVING COUNT(*) = 1
     ORDER BY num DESC
     LIMIT 1)
,NULL) AS num

620

select *
from cinema
where mod(id, 2) = 1 and description != 'boring'
order by rating DESC

627

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

1050

select actor_id, director_id 
from actordirector 
group by actor_id, director_id 
having count(*) >= 3

*1086

select
p.product_name,
s.year,
s.price
from sales s
join product p
on s.product_id = p.product_id;

*1069

select product_id, sum(quantity) total_quantity from sales group by product_id;

*1075

SELECT p.project_id, ROUND(avg(e.experience_years),2) AS average_years
        FROM Project p
        JOIN Employee e
        ON p.employee_id = e.employee_id
        GROUP BY p.project_id

*1076

select project_id
from Project p
group by project_id
having count(*) = 
(select count(*) c from Project group by project_id order by c desc limit 1)

1082

SELECT seller_id
FROM Sales
GROUP BY seller_id
HAVING SUM(price) = (SELECT SUM(price)
                     FROM Sales
                     GROUP BY seller_id
                     ORDER BY 1 DESC
                     LIMIT 1 )

1083

SELECT s.buyer_id
FROM Sales s
JOIN Product p
ON s.product_id = p.product_id
GROUP BY s.buyer_id
HAVING SUM(p.product_name = 'S8') > 0 AND SUM(p.product_name = 'iphone') = 0

1084

SELECT product_id, product_name
FROM product
WHERE product_id NOT IN
(SELECT product_id
FROM sales
WHERE sale_date NOT BETWEEN '2019-01-01' AND '2019-03-31');

1113

SELECT extra AS report_reason, COUNT(DISTINCT post_id) AS report_count
FROM Actions
WHERE action = 'report' AND action_date = '2019-07-04'
GROUP BY extra

1142

SELECT IFNULL(ROUND(COUNT(DISTINCT session_id)/COUNT(DISTINCT user_id),2),0.00) AS average_sessions_per_user
FROM Activity 
WHERE activity_date >= '2019-06-28' and activity_date <= '2019-07-27';  

*1148

SELECT DISTINCT author_id AS id
FROM Views
WHERE author_id = viewer_id
ORDER BY id

1173

SELECT ROUND( (a.immediate / b.total) * 100.0, 2) AS immediate_percentage FROM
(SELECT COUNT(*) AS immediate FROM delivery WHERE order_date = customer_pref_delivery_date) AS a,
(SELECT COUNT(*) AS total FROM delivery) AS b

SELECT ROUND(100*AVG(order_date = customer_pref_delivery_date), 2) AS immediate_percentage
FROM Delivery;

1179

select id, 
    sum(case when month = 'jan' then revenue else null end) as Jan_Revenue,
    sum(case when month = 'feb' then revenue else null end) as Feb_Revenue,
    sum(case when month = 'mar' then revenue else null end) as Mar_Revenue,
    sum(case when month = 'apr' then revenue else null end) as Apr_Revenue,
    sum(case when month = 'may' then revenue else null end) as May_Revenue,
    sum(case when month = 'jun' then revenue else null end) as Jun_Revenue,
    sum(case when month = 'jul' then revenue else null end) as Jul_Revenue,
    sum(case when month = 'aug' then revenue else null end) as Aug_Revenue,
    sum(case when month = 'sep' then revenue else null end) as Sep_Revenue,
    sum(case when month = 'oct' then revenue else null end) as Oct_Revenue,
    sum(case when month = 'nov' then revenue else null end) as Nov_Revenue,
    sum(case when month = 'dec' then revenue else null end) as Dec_Revenue
from department
group by id
order by id

1211

SELECT 
    query_name,
    ROUND(AVG(rating / position), 2) AS quality,
    ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage 
FROM 
    Queries
GROUP BY 
    query_name

1241

SELECT s.sub_id AS post_id,
(SELECT COUNT(DISTINCT(s1.sub_id)) FROM Submissions s1 WHERE s1.parent_id = s.sub_id) 
AS number_of_comments
FROM Submissions s
WHERE s.parent_id IS null
GROUP BY s.sub_id

1251

SELECT a.product_id,ROUND(SUM(b.units*a.price)/SUM(b.units),2) as average_price
FROM Prices as a
JOIN UnitsSold as b
ON a.product_id=b.product_id AND (b.purchase_date BETWEEN a.start_date AND a.end_date)
GROUP BY product_id;

*1350

SELECT s.id, s.name
FROM Students s
WHERE s.department_id NOT in (
SELECT d.id FROM Departments d
)

1280

SELECT a.student_id,a.student_name,b.subject_name,COUNT(c.subject_name) as attended_exams
FROM Students as a
JOIN Subjects as b
LEFT JOIN Examinations as c
ON a.student_id=c.student_id AND b.subject_name=c.subject_name
GROUP BY a.student_id,b.subject_name;

1294

SELECT a.country_name,
CASE 
WHEN AVG(weather_state)<=15 THEN "Cold"
WHEN AVG(weather_State)>=25 THEN "Hot"
ELSE "Warm"
END
AS weather_type 
FROM Countries AS a
JOIN Weather AS b
ON a.country_id = b.country_id
WHERE MONTH(b.day) = 11
GROUP BY b.country_id

1303

SELECT employee_id,
COUNT(employee_id) OVER (PARTITION BY team_id) team_size
FROM EMPLOYEE

SELECT employee_id, team_size
FROM Employee AS e
LEFT JOIN (
      SELECT team_id, COUNT(DISTINCT employee_id) AS team_size
      FROM Employee
      GROUP BY team_id
) AS teams
ON e.team_id = teams.team_id

1322

SELECT ad_id, 
IFNULL(ROUND(SUM(CASE WHEN ACTION = 'Clicked' THEN 1 ELSE 0 END) / SUM(CASE WHEN ACTION = 'Clicked' OR ACTION = 'Viewed' THEN 1 ELSE 0 END) * 100, 2), 0) AS ctr
FROM Ads
GROUP BY ad_id
ORDER BY ctr DESC, ad_id

1327

SELECT p.product_name,SUM(o.unit) AS unit
FROM Products p
JOIN Orders o
ON p.product_id = o.product_id
WHERE LEFT(order_date, 7) = '2020-02'
GROUP BY p.product_id
HAVING sum(o.unit) >= 100
ORDER BY unit DESC

1270

SELECT a.employee_id as EMPLOYEE_ID FROM Employees as a # those whose boss is 1
WHERE a.employee_id!=1 AND a.manager_id=1
UNION
SELECT b.employee_id FROM Employees as b #those whose boss' boss is 1
WHERE b.manager_id IN
(
    SELECT a.employee_id FROM Employees as a
    WHERE a.employee_id!=1 AND a.manager_id=1    
)
UNION
SELECT c.employee_id FROM Employees as c #those whose boss' boss' boss is 1
WHERE c.manager_id IN
(
    SELECT b.employee_id FROM Employees as b
    WHERE b.manager_id IN
    (
        SELECT a.employee_id FROM Employees as a
        WHERE a.employee_id!=1 AND a.manager_id=1    
    )
)
ORDER BY EMPLOYEE_ID;

select employee_id from employees
where manager_id in
(select employee_id from employees where manager_id in
(select employee_id from employees where manager_id = 1))
and employee_id !=1;

1308

SELECT gender, day, 
       SUM(score_points) OVER(PARTITION BY gender ORDER BY day) AS total
FROM Scores

SELECT s1.gender, s1.day, SUM(s2.score_points) AS total
FROM Scores AS s1,
     Scores AS s2
WHERE s1.gender = s2.gender AND s2.day <= s1.day
GROUP BY s1.gender, s1.day
ORDER BY s1.gender, s1.day

1285 **

SELECT min(log_id) as start_id, max(log_id) as end_id
FROM
(SELECT log_id, ROW_NUMBER() OVER(ORDER BY log_id) as num
FROM Logs) a
GROUP BY log_id - num

NOTES: 1 1 2 2 3 3 7 4 8 5 10 6 the difference of (log_id - num) will change whenever a log_id is skipped. The rows with same difference (log_id - num), should be a continuous series. Then we just need to find the min, and max for that series as start/ end id. It’s easier to understand if you draft it out with column log_in & row_number.

1077

SELECT p.project_id, e.employee_id
from project as p
join employee as e on e.employee_id = p.employee_id
where (p.project_id, e.experience_years) in
(SELECT p.project_id, max(e.experience_years)
from project as p
join employee as e on e.employee_id = p.employee_id
group by project_id)

1126

select 
business_id
from events as a
join
    (
    select event_type, avg(occurences) as av
    from events
    group by event_type
    ) as b
on a.event_type = b.event_type
where a.occurences > b.av
group by business_id
having count(*)>1;

1204

SELECT q1.person_name
FROM Queue q1 
JOIN Queue q2 
ON q1.turn >= q2.turn
GROUP BY q1.turn
HAVING SUM(q2.weight) <= 1000
ORDER BY SUM(q2.weight) DESC
LIMIT 1

1321.Same problem as 1204

SELECT a.visited_on AS visited_on, SUM(b.day_sum) AS amount,
       ROUND(AVG(b.day_sum), 2) AS average_amount
FROM
  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) a,
  (SELECT visited_on, SUM(amount) AS day_sum FROM Customer GROUP BY visited_on ) b
WHERE DATEDIFF(a.visited_on, b.visited_on) BETWEEN 0 AND 6
GROUP BY a.visited_on
HAVING COUNT(b.visited_on) = 7

1045

SELECT customer_id
FROM customer c
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key)=(SELECT COUNT(DISTINCT product_key) FROM product)

1193

SELECT LEFT(trans_date, 7) AS month, country,
    COUNT(1) AS trans_count,
    SUM(CASE WHEN state = 'approved' then 1 else 0 end) as approved_count,
    sum(amount) as trans_total_amount, 
    sum(case when state = 'approved' then amount else 0 end) as approved_total_amount
from Transactions
group by country, left(trans_date, 7)

608

# Write your MySQL query statement below
SELECT
    id, 'Root' AS Type
FROM
    tree
WHERE
    p_id IS NULL

UNION

SELECT
    id, 'Leaf' AS Type
FROM
    tree
WHERE
    id NOT IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
    AND p_id IS NOT NULL

UNION

SELECT
    id, 'Inner' AS Type
FROM
    tree
WHERE
    id IN (SELECT DISTINCT
            p_id
        FROM
            tree
        WHERE
            p_id IS NOT NULL)
        AND p_id IS NOT NULL
ORDER BY id;

1164

select distinct product_id, 10 as price
from Products
group by product_id
having (min(change_date) > "2019-08-16")

union

select p2.product_id, new_price
from Products p2
where (p2.product_id, p2.change_date) in

(
select product_id, max(change_date) as recent_date
from Products
where change_date <= "2019-08-16"
group by product_id
)

626

#For students with odd id, the new id is (id+1) after switch unless it is the last seat. And for students #with even id, the new id is (id-1). In order to know how many seats in total, we can use a subquery:
SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
FROM
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM
        seat) AS seat_counts
ORDER BY id ASC;

1158

SELECT user_id AS buyer_id, join_date, COALESCE(COUNT(o.order_id),0) AS orders_in_2019
FROM Users u
LEFT JOIN Orders o ON u.user_id = o.buyer_id AND YEAR(order_date)='2019'
GROUP BY user_id
ORDER BY user_id

612

# Write your MySQL query statement below
SELECT
    ROUND(SQRT(MIN((POW(p1.x - p2.x, 2) + POW(p1.y - p2.y, 2)))), 2) AS shortest
FROM
    point_2d p1
        JOIN
    point_2d p2 ON p1.x != p2.x OR p1.y != p2.y

1174

# Write your MySQL query statement below
SELECT
    ROUND(100*SUM(CASE WHEN order_date = customer_pref_delivery_date THEN 1
    ELSE 0 END)/ COUNT(distinct customer_id) ,2) AS immediate_percentage
FROM
    Delivery
WHERE
    (customer_id, order_date)
IN
(SELECT
    customer_id, min(order_date) as min_date
FROM
    Delivery
GROUP BY
    customer_id
)

112

1112

SELECT student_id, MIN(course_id) AS course_id, grade
FROM Enrollments
WHERE (student_id, grade) IN
(SELECT student_id, MAX(grade)
FROM Enrollments
GROUP BY student_id)
GROUP BY student_id
ORDER BY student_id

1264

SELECT DISTINCT page_id AS recommended_page
FROM Likes
WHERE user_id IN (
    SELECT user2_id AS friend_id FROM Friendship WHERE user1_id = 1
    UNION
    SELECT user1_id AS friend_id FROM Friendship WHERE user2_id = 1) 
    AND
    page_id NOT IN (
      SELECT page_id FROM Likes WHERE user_id = 1
    )

1070

SELECT product_id, year AS first_year, quantity, price
FROM Sales
WHERE (product_id, year) IN (
SELECT product_id, MIN(year) as year
FROM Sales
GROUP BY product_id) ;

*1149

SELECT DISTINCT viewer_id AS id
FROM Views
GROUP BY viewer_id, view_date
HAVING COUNT(DISTINCT article_id) > 1

1107

select login_date, count(user_id) as user_count
from
(select user_id, min(activity_date) as login_date
from Traffic
where activity = 'login'
group by user_id) t
where datediff('2019-06-30', login_date) <= 90
group by login_date

1205

SELECT month, country, SUM(CASE WHEN state = "approved" THEN 1 ELSE 0 END) AS approved_count, SUM(CASE WHEN state = "approved" THEN amount ELSE 0 END) AS approved_amount, SUM(CASE WHEN state = "back" THEN 1 ELSE 0 END) AS chargeback_count, SUM(CASE WHEN state = "back" THEN amount ELSE 0 END) AS chargeback_amount
FROM
(
    SELECT LEFT(chargebacks.trans_date, 7) AS month, country, "back" AS state, amount
    FROM chargebacks
    JOIN transactions ON chargebacks.trans_id = transactions.id
    UNION ALL
    SELECT LEFT(trans_date, 7) AS month, country, state, amount
    FROM transactions
    WHERE state = "approved"
) s
GROUP BY month, country

1098

select b.book_id, b.name
from books b 
left join orders o
on b.book_id = o.book_id and dispatch_date between '2018-06-23' and '2019-06-23'
where datediff('2019-06-23', available_from) > 30
group by b.book_id, b.name
having ifnull(sum(quantity),0) <10;

1132

SELECT ROUND(AVG(cnt), 2) AS average_daily_percent FROM
(
    SELECT (COUNT(DISTINCT r.post_id)/ COUNT(DISTINCT a.post_id))*100  AS cnt
FROM Actions a
LEFT JOIN Removals r
ON a.post_id = r.post_id
WHERE extra='spam' and action = 'report'
GROUP BY action_date)tmp

614

SELECT f1.follower as follower, COUNT(DISTINCT f2.follower) as num
FROM follow f1 join follow f2
on f1.follower=f2.followee
GROUP BY f2.followee

1225**

615

select department_salary.pay_month, department_id,
case
  when department_avg>company_avg then 'higher'
  when department_avg<company_avg then 'lower'
  else 'same'
end as comparison
from
(
  select department_id, avg(amount) as department_avg, date_format(pay_date, '%Y-%m') as pay_month
  from salary join employee on salary.employee_id = employee.employee_id
  group by department_id, pay_month
) as department_salary
join
(
  select avg(amount) as company_avg,  date_format(pay_date, '%Y-%m') as pay_month from salary group by date_format(pay_date, '%Y-%m')
) as company_salary
on department_salary.pay_month = company_salary.pay_month

618*

SELECT 
    America, Asia, Europe
FROM
    (SELECT @as:=0, @am:=0, @eu:=0) t,
    (SELECT 
        @as:=@as + 1 AS asid, name AS Asia
    FROM
        student
    WHERE
        continent = 'Asia'
    ORDER BY Asia) AS t1
        RIGHT JOIN
    (SELECT 
        @am:=@am + 1 AS amid, name AS America
    FROM
        student
    WHERE
        continent = 'America'
    ORDER BY America) AS t2 ON asid = amid
        LEFT JOIN
    (SELECT 
        @eu:=@eu + 1 AS euid, name AS Europe
    FROM
        student
    WHERE
        continent = 'Europe'
    ORDER BY Europe) AS t3 ON amid = euid

1097

select distinct install_dt,count(install_dt) as installs,round(ifnull(count(day1),0)/count(install_dt),2) as Day1_retention
from
    (select a1.player_id,a1.install_dt, a2.event_date as day1
     from
         (select player_id,min(event_date) as install_dt
         from activity
         group by player_id) as a1
    left join activity a2
    on a1.player_id=a2.player_id and a1.install_dt=a2.event_date-1) as a3
group by install_dt

1159

SELECT user_id as seller_id, 
if(i.item_brand = u.favorite_brand, "yes", "no") as 2nd_item_fav_brand
from Users u left join 
    (SELECT o1.seller_id, o1.item_id, o1.order_date
    FROM Orders o1 JOIN Orders o2
    ON o1.seller_id = o2.seller_id AND o1.order_date > o2.order_date
    GROUP BY o1.seller_id, o1.order_date
    HAVING count(o1.order_id) = 1) t
ON u.user_id = t.seller_id
LEFT JOIN Items i
ON t.item_id = i.item_id
ORDER BY u.user_id;

1194

select group_id as GROUP_ID, min(player_id) as PLAYER_ID
from Players,
    (select player, sum(score) as score from
        (select first_player as player, first_score as score from Matches
        union all
        select second_player, second_score from Matches) s
    group by player) PlayerScores
where Players.player_id = PlayerScores.player and (group_id, score) in
    (select group_id, max(score)
    from Players,
        (select player, sum(score) as score from
            (select first_player as player, first_score as score from Matches
            union all
            select second_player, second_score from Matches) s
        group by player) PlayerScores
    where Players.player_id = PlayerScores.player
    group by group_id)
group by group_id