-- Написать SQL-запросы (MySQL):
create table `groups`
(
id int not null primary key,
name varchar(50) not null
);
create table users
(
id int not null primary key,
group_id int not null,
invited_by_user_id int not null,
name varchar(50) not null,
posts_qty int not null,
constraint fk_users_group_id
foreign key (group_id) references `groups` (id)
on update cascade on delete cascade
);
insert into `groups`
(id, name)
values
(1, 'Группа 1'),
(2, 'Группа 2');
insert into users
(id, group_id, invited_by_user_id, name, posts_qty)
values
(1, 1, 0, 'Пользователь 1', 0),
(2, 1, 1, 'Пользователь 2', 2),
(3, 1, 2, 'Пользователь 3', 5),
(4, 2, 3, 'Пользователь 4', 7),
(5, 2, 4, 'Пользователь 5', 1);
-- Выборки пользователей, у которых количество постов больше, чем у пользователя их пригласившего.
SELECT users.*, invited_by.posts_qty
FROM users
JOIN users invited_by ON users.invited_by_user_id = invited_by.id
WHERE users.posts_qty > invited_by.posts_qty;
-- Выборки пользователей, имеющих максимальное количество постов в своей группе.
SELECT users.*
FROM users
JOIN (
SELECT group_id, MAX(posts_qty) posts_qty FROM users GROUP BY group_id
) max_posts ON
users.posts_qty = max_posts.posts_qty AND users.group_id = max_posts.group_id;
-- Выборки групп, количество пользователей в которых превышает 10000.
SELECT g.id, g.name
FROM `groups` g
JOIN `users` u ON u.group_id = g.id
GROUP BY g.id, g.name
HAVING COUNT(DISTINCT u.id) > 10000;
-- Выборки пользователей, у которых пригласивший их пользователь из другой группы.
SELECT users.*, invited_by.posts_qty
FROM users
JOIN users invited_by ON users.invited_by_user_id = invited_by.id
WHERE users.group_id <> invited_by.group_id;
-- Выборки групп с максимальным количеством постов у пользователей.
WITH group_posts AS (
SELECT g.id, g.name, SUM(posts_qty) posts
FROM `groups` g
JOIN `users` u ON u.group_id = g.id
GROUP BY g.id, g.name
) SELECT *
FROM group_posts
ORDER BY RANK() OVER (ORDER BY posts DESC)
LIMIT 1;
Telegram: DB developers channel