User Payments

CREATE TABLE users (
  id BIGINT NOT NULL,
  name varchar NOT NULL UNIQUE,
  age int NOT NULL,
  PRIMARY KEY (id)
);

insert into users (id, name, age) values
(1, 'Ann', 25),
(2, 'Mary', 25),
(3, 'Boris', 30),
(4, 'John', 30),
(5, 'Ted', 30),
(6, 'Kate', 35),
(7, 'Sara', 35),
(8, 'Victoria', 35),
(9, 'Robin', 35);

CREATE TABLE payments (
  id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  date TIMESTAMP NOT NULL,
  amount int NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT fk_payments_users FOREIGN KEY (user_id) REFERENCES users (id)
);

insert into payments (id, user_id, date, amount) values
(1, 1, '2018-02-25', 100),
(2, 1, '2017-12-05', 10),
(3, 2, '2020-03-12', 50),
(4, 2, '2018-05-30', 30),
(5, 3, '2021-06-11', 150),
(6, 3, '2022-02-01', 5),
(7, 4, '2017-07-02', 40),
(8, 4, '2020-02-03', 125),
(9, 5, '2022-06-27', 70),
(10, 5, '2021-09-23', 75),
(11, 6, '2021-11-15', 150),
(12, 6, '2018-10-17', 10),
(13, 7, '2020-04-25', 20),
(14, 7, '2022-08-18', 10),
(15, 8, '2018-11-27', 100),
(16, 8, '2017-05-07', 150),
(17, 9, '2019-01-08', 135),
(18, 9, '2021-06-19', 70),
(19, 9, '2022-06-19', 75),
(20, 1, '2018-07-03', 25);

-- Please, select average payment per user age.
select u.age, avg(p.amount) from users u
join payments p on p.user_id = u.id
group by u.age

-- Please, select all users without payments after a specific date.
select id, name from users where id not in (select user_id from payments where date > '2019-12-31');

select u.id, min(u.name) as name from users u
join payments p on p.user_id = u.id
group by u.id
having max(p.date) <= '2019-12-31';

-- Select all users who have made more than 2 payments
select count(*), u.id, max(u.name) as name from users u
join payments p on p.user_id = u.id
group by u.id
having count(*) > 2;