sql

CREATE TABLE Movies (
  movie_id INT PRIMARY KEY,
  title VARCHAR(100)
);

CREATE TABLE Users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE MovieRating (
  movie_id INT,
  user_id INT,
  rating INT,
  created_at DATE,
  PRIMARY KEY (movie_id, user_id)
);


drop table Movies;


INSERT INTO Movies (movie_id, title) VALUES
  (1, 'Avengers'),
  (2, 'Frozen 2'),
  (3, 'Joker');

INSERT INTO Users (user_id, name) VALUES
  (1, 'Daniel'),
  (2, 'Monica'),
  (3, 'Maria'),
  (4, 'James');

INSERT INTO MovieRating (movie_id, user_id, rating, created_at) VALUES
  (1, 1, 3, '2020-01-12'),
  (1, 2, 4, '2020-02-11'),
  (1, 3, 2, '2020-02-12'),
  (1, 4, 1, '2020-01-01'),
  (2, 1, 5, '2020-02-17'),
  (2, 2, 2, '2020-02-01'),
  (2, 3, 2, '2020-03-01'),
  (3, 1, 3, '2020-02-22'),
  (3, 2, 4, '2020-02-25');


select r1.results from (

select u.name as results from  Users as u left join MovieRating as  m on
 u.user_id=m.user_id
group by u.user_id,u.name
order by count(u.user_id) desc,u.name asc
limit 1

) as r1

union all

select r2.results from (
  select m2.title as results from
  Movies as m2 left join MovieRating as m on
  m.movie_id=m2.movie_id
  and m.created_at like '2020-02%'
  group by m2.title
  order by avg(m.rating) desc,m2.title asc
  limit 1
) r2;





-- 查找评论电影数量最多的用户名
SELECT u.name AS results
FROM Users u
JOIN (SELECT user_id, COUNT(DISTINCT movie_id) AS num_movies
FROM MovieRating GROUP BY user_id
HAVING num_movies = (SELECT MAX(num_movies)
FROM (SELECT COUNT(DISTINCT movie_id) AS num_movies
FROM MovieRating GROUP BY user_id) t)) m ON u.user_id = m.user_id
ORDER BY u.name
LIMIT 1;


SELECT m.title AS results
FROM Movies m
JOIN (
    SELECT movie_id, AVG(rating) AS avg_rating
    FROM MovieRating
    WHERE created_at >= '2020-02-01' AND created_at < '2020-03-01'
    GROUP BY movie_id
    HAVING avg_rating = (
        SELECT MAX(avg_rating)
        FROM (
            SELECT movie_id, AVG(rating) AS avg_rating
            FROM MovieRating
            WHERE created_at >= '2020-02-01' AND created_at < '2020-03-01'
            GROUP BY movie_id
        ) t
    )
) r ON m.movie_id = r.movie_id
ORDER BY m.title
LIMIT 1;



SELECT results
FROM (
  SELECT name AS results, RANK() OVER(ORDER BY COUNT(*) DESC, name) AS RANKING
  FROM Users
  INNER JOIN MovieRating USING(user_id)
  GROUP BY user_id
  UNION ALL
  SELECT title AS results, RANK() OVER(ORDER BY AVG(rating) DESC, title) AS RANKING
  FROM MovieRating
  INNER JOIN Movies USING(movie_id)
  WHERE DATE_FORMAT(created_at, '%Y-%m') = '2020-02'
  GROUP BY movie_id
) T
WHERE T.RANKING = 1

窗口函数。

第一个子查询选择的是评论电影数量最多的用户名。通过内连接（INNER JOIN）Users和MovieRating表，使用user_id进行连接，并使用GROUP BY对user_id进行分组。然后使用RANK() OVER进行排名，根据评论数量降序排列，并根据用户名的字典序升序排列（如果评论数量相同）。 第二个子查询选择的是在2020年2月份平均评分最高的电影名称。通过内连接Movies和MovieRating表，使用movie_id进行连接，并使用WHERE子句筛选出2020年2月份的数据。然后使用GROUP BY对movie_id进行分组，计算平均评分。再使用RANK() OVER进行排名，根据平均评分降序排列，并根据电影名称的字典序升序排列（如果平均评分相同）。

sql
CREATE TABLE Employees (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE EmployeeUNI (
  id INT,
  unique_id INT,
  PRIMARY KEY (id, unique_id)
);



INSERT INTO Employees (id, name) VALUES
  (1, 'Alice'),
  (7, 'Bob'),
  (11, 'Meir'),
  (90, 'Winston'),
  (3, 'Jonathan');

INSERT INTO EmployeeUNI (id, unique_id) VALUES
  (3, 1),
  (11, 2),
  (90, 3);


select unique_id,name from
Employees as e1 left join
EmployeeUNI as e2 on e1.id=e2.id ;