SQL JOB INTERVIEW. EPISODE 3.

This test comes from Promsvyazbank.

Entry table:

Имеется таблица покупок клиентов:
• TRANS_DATE (Дата)
• CLIENT (ID клиента)
• MCC (группа по типу торговой организации, Супермаркеты, Обувь, Авиалинии, Кино…)
• AMOUNT (сумма операции)
Таблица включает операции за несколько месяцев

Задача 1
Найти вторую по объему категорию трат у каждого клиента.

Задача 2
Вывести категории по клиенту, в которых он тратит значительно меньше (настраиваемый параметр) чем другие клиенты с похожим суммарным объемом трат (т.е. категории в которых они гипотетически могли бы подрасти). Ввести N категорий с наибольшим разрывом.
Допускается создание промежуточных таблиц

Задача 3
Есть 2 таблицы, связь по Client_ID:
• Вклады – Deposit
o ID
o Client_ID
o Begin_Date
o End_Date_Plan (плановая дата окончания)
o End_Date_Fact (фактическая дата окончания)
• Кред.заявки – Order
o ID
o Client_ID
o Order_Date
Есть гипотезы:
• В целом вкладчики слабо заинтересованы в кредитах
• При этом есть повышенная заинтересованность в кредите в момент закрытия вклада (например, выводят средства под крупную покупку).
Задача:
1. Наглядно доказать / опровергнуть эти гипотезы (диаграммами, таблицами)
2. Привести SQL, их формирующий

As always, gotta create a sample table at SQLFiddle.com to facilitate thinking:

CREATE TABLE PSB_transact (
TRANS_DATE DATE,
CLIENT VARCHAR (32),
MCC VARCHAR (32),
AMOUNT INTEGER );

INSERT INTO  PSB_transact VALUES('2018-06-01','0001','food',6516);
INSERT INTO  PSB_transact VALUES('2018-06-01','0001','shopping',2026);
INSERT INTO  PSB_transact VALUES('2018-06-01','0002','food',8799);
INSERT INTO  PSB_transact VALUES('2018-06-01','0002','transport',5325);
INSERT INTO  PSB_transact VALUES('2018-06-02','0001','food',5991);
INSERT INTO  PSB_transact VALUES('2018-06-02','0001','transport',5738);
INSERT INTO  PSB_transact VALUES('2018-06-02','0002','shopping',1838);
INSERT INTO  PSB_transact VALUES('2018-06-02','0002','leisure',7400);
INSERT INTO  PSB_transact VALUES('2018-06-03','0001','shopping',8922);
INSERT INTO  PSB_transact VALUES('2018-06-03','0001','transport',4726);
INSERT INTO  PSB_transact VALUES('2018-06-03','0002','gas',7354);
INSERT INTO  PSB_transact VALUES('2018-06-03','0002','gas',6248);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','shopping',7573);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','gas',8288);
INSERT INTO  PSB_transact VALUES('2018-06-04','0002','transport',281);
INSERT INTO  PSB_transact VALUES('2018-06-04','0002','food',2088);
INSERT INTO  PSB_transact VALUES('2018-06-05','0003','shopping',4665);
INSERT INTO  PSB_transact VALUES('2018-06-05','0004','gas',4487);
INSERT INTO  PSB_transact VALUES('2018-06-05','0001','transport',3142);
INSERT INTO  PSB_transact VALUES('2018-06-05','0002','leisure',1799);
INSERT INTO  PSB_transact VALUES('2018-06-03','0003','gas',3801);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','transport',4103);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','leisure',4405);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','food',4707);
INSERT INTO  PSB_transact VALUES('2018-06-03','0004','leisure',5009);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','food',5311);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','transport',5613);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','shopping',5915);
INSERT INTO  PSB_transact VALUES('2018-06-03','0003','gas',3801);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','transport',4103);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','leisure',4405);
INSERT INTO  PSB_transact VALUES('2018-06-04','0003','food',4707);
INSERT INTO  PSB_transact VALUES('2018-06-03','0004','leisure',5009);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','food',5311);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','transport',5613);
INSERT INTO  PSB_transact VALUES('2018-06-04','0004','shopping',5915);
INSERT INTO  PSB_transact VALUES('2018-07-25','0003','food',4757);
INSERT INTO  PSB_transact VALUES('2018-07-13','0004','leisure',8009);
INSERT INTO  PSB_transact VALUES('2018-07-05','0004','food',6811);
INSERT INTO  PSB_transact VALUES('2018-07-01','0004','transport',9613);
INSERT INTO  PSB_transact VALUES('2018-07-09','0004','shopping',4115);

The tasks are below:

Task 1:
Найти вторую по объему категорию трат у каждого клиента.

This one is done by row number trick:

select
client, mcc, rn
from
(
  select
  client
  ,mcc
  ,cat_sum
  ,ROW_NUMBER() OVER (PARTITION BY client ORDER BY cat_sum DESC) as RN
  from
  (
    SELECT
    client
    ,mcc
    ,sum(AMOUNT) as cat_sum
    FROM PSB_transact as t1
    group by client, mcc
    order by client
  ) as t1
  order by client
) as t1
where 1=1
and rn=2