--Задача 1
--создаем таблицу с информаией о клиенте
CREATE TABLE Clients
( Ow_id NUMBER,
Birth_dt DATE,
Email_flg NUMBER,
Mobile_app_flg NUMBER
);
--наполняем ее даными
INSERT ALL
INTO Clients (Ow_id, Birth_dt, Email_flg, Mobile_app_flg) VALUES (123, '17.06.2022', 0, 1)
INTO Clients (Ow_id, Birth_dt, Email_flg, Mobile_app_flg) VALUES (124, '25.06.2022', 1, 1)
INTO Clients (Ow_id, Birth_dt, Email_flg, Mobile_app_flg) VALUES (142, '16.06.2022', 1, 1)
INTO Clients (Ow_id, Birth_dt, Email_flg, Mobile_app_flg) VALUES (143, '17.06.2022', 0, 1)
INTO Clients (Ow_id, Birth_dt, Email_flg, Mobile_app_flg) VALUES (144, '17.06.2022', 0, 0)
SELECT * FROM Clients;
--создаем таблицу с информаией о партнерах
CREATE TABLE Transactions
( Ow_id NUMBER,
Partner_id NUMBER,
POS NUMBER,
CASH_DT DATE
);
--наполняем ее даными
INSERT ALL
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (123, 5555, 1001, '17.06.2022')
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (123, 1111, 200, '25.06.2022')
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (123, 1111, 500, '16.06.2022')
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (123, 5555, 800, '17.06.2022')
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (142, 1111, 600, '17.06.2022')
INTO Transactions (Ow_id, Partner_id, POS, CASH_DT) VALUES (142, 5555, 700, '29.06.2009')
SELECT * FROM Transactions;
select
C.Ow_id,
month(t.cash_dt) as month_of_cash,
sum(t.POS) as sum_pos
FROM
Clients C
JOIN Transactions T ON T.Ow_id = C.Ow_id
where
CASH_DT between ADD_MONTHS (TRUNC(SYSDATE), -2) and ADD_MONTHS (TRUNC(SYSDATE), -1)
group by
C.Ow_id,
month (t.cash_dt)
Telegram: DB developers channel