Задания SQL Flashcards by Astral Goose

Решил

Условие:
В таблицу OrderPosition попали дубли. Необходимо их удалить (оставить любую строку) двумя способами
Ключ – order_id, product_id.

CREATE TABLE OrderPosition
(
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
product_price DECIMAL(20,6)
);

INSERT INTO OrderPosition (order_id, product_id, quantity, product_price)
VALUES
(14324, 459871, 1, 1540.99),
(14324, 459871, 1, 1540.99),
(14324, 459871, 1, 1540.99),
(14325, 98712, 3, 599.99),
(14325, 98712, 3, 599.99),
(16788, 598942, 1, 199.99),
(16789, 598943, 1, 5200.00);

Теги: #озон

WITH duplicates AS (
SELECT
ctid,
ROW_NUMBER() OVER (
PARTITION BY order_id, product_id
ORDER BY ctid – жёстко базируем порядок на физическом адресе
) AS rn
FROM OrderPosition
)
DELETE FROM OrderPosition
WHERE ctid IN (
SELECT ctid
FROM duplicates
WHERE rn > 1 – удаляем все, у кого номер > 1
);

DELETE FROM OrderPosition
WHERE ctid NOT IN (
SELECT MIN(ctid)
FROM OrderPosition
GROUP BY order_id, product_id
);

How well did you know this?

Not at all

Perfectly

Таблица 1 | Таблица 2

1 → 1
2 → 1
2 → 2
3 → 2
5 → 7
null → 8
null → null

Вывести результат join таблиц

Теги: #озон

Inner
1
1
2
2
2
2
Left
1
1
2
2
2
2
3
5
7
null
Right
1
1
2
2
2
2
7
8
null
Full
1
1
2
22
2
3
5
7
8
Null
Null
Null

Cross

How well did you know this?

Not at all

Perfectly

есть 2 таблицы: в одной 5 записей, во второй 7
Вывести минимальное и максимальное количество строк при пересечении

inner join 
left join
right
cross
full

Теги: #озон

inner join
0 35
left join
5 35
right
7 35
cross
35
full
7 35

How well did you know this?

Not at all

Perfectly

ошибка синтаксиса

Вывести те значения, которые повторяются подряд не менее трех раз
CREATE TABLE IF NOT EXISTS Logs (num int);
INSERT INTO Logs (num) VALUES (134),(34),(4),(4),(4),(4),(4),(7),(14),(4),(4),(4),(2),(2),(2)

Теги: #ЛеруаМерлен

WITH LaggedLogs AS (
SELECT num,
LAG(num, 1) OVER (ORDER BY (SELECT NULL)) AS prev1,
LAG(num, 2) OVER (ORDER BY (SELECT NULL)) AS prev2
FROM Logs
)
SELECT DISTINCT num
FROM LaggedLogs
WHERE num = prev1 AND num = prev2;

How well did you know this?

Not at all

Perfectly

решил, но не с первого раза. Заруинил синтаксис

/*
order_id | store_id | item_id | supplier_id | price | quantity | dt
———+———-+———+————-+——-+———-+————————
1 | 1 | 10001 | 1 | 20 | 1 | 2023-10-11 13:54:00.000
2 | 2 | 10001 | 2 | 30 | 2 | 2023-11-11 17:14:05.000
2 | 3 | 10002 | 2 | 100 | 3 | 2023-09-01 09:32:50.000
3 | 3 | 10002 | 3 | 130 | 1 | 2023-10-21 14:51:45.000
supplier_id | supplier_name | supplier_type
————+—————+————–
1 | Supplier 1 | WAREHOUSE
2 | Supplier 2 | NULL
3 | Supplier 3 | SUPPLIER
4 | Supplier 4 | SUPPLIER
*/

–Напишите запрос, который для всех поставщиков с типом (supplier_type) отличным от “WAREHOUSE” выдаст идентификатор поставщика и сумму заказов у этого поставщика. В запросе не использовать оконные функции.

DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
order_id INT,
store_id INT,
item_id INT,
supplier_id INT,
price DECIMAL,
quantity INT,
dt TIMESTAMP
);

INSERT INTO orders (order_id, store_id, item_id, supplier_id, price, quantity, dt) VALUES
(1, 1, 10001, 1, 20, 1, ‘2023-10-11 13:54:00’),
(2, 2, 10001, 2, 30, 2, ‘2023-11-11 17:14:05’),
(2, 3, 10002, 2, 100, 3, ‘2023-09-01 09:32:50’),
(3, 3, 10002, 3, 130, 1, ‘2023-10-21 14:51:45’);

CREATE TABLE suppliers (
supplier_id INT PRIMARY KEY,
supplier_name TEXT,
supplier_type TEXT
);

INSERT INTO suppliers (supplier_id, supplier_name, supplier_type) VALUES
(1, ‘Supplier 1’, ‘WAREHOUSE’),
(2, ‘Supplier 2’, NULL),
(3, ‘Supplier 3’, ‘SUPPLIER’),
(4, ‘Supplier 4’, ‘SUPPLIER’);

Теги: #ЛеруаМерлен

SELECT
supplier_type,
SUM(quantity * price) AS total
FROM
suppliers s
LEFT JOIN
orders o
ON
s.supplier_id = o.supplier_id
WHERE
s.supplier_type != ‘WAREHOUSE’ OR s.supplier_type IS NULL
GROUP BY
supplier_type

How well did you know this?

Not at all

Perfectly

Ошибся в синтаксисе

SQL: Дан лог заездов/выездов в гостиницу. Лог состоит из записей (ts, user_id, event_type).
Надо найти сколько было постояльцев на каждый день существования гостиницы. Число постояльцев считается на конец дня. Вывести без дублей

CREATE TABLE hotel_log (
ts TIMESTAMP,
user_id INT,
event_type TEXT
);

INSERT INTO hotel_log (ts, user_id, event_type) VALUES
(‘2023-10-01 14:00:00’, 1, ‘CHECK_IN’),
(‘2023-10-01 18:30:00’, 2, ‘CHECK_IN’),
(‘2023-10-02 10:00:00’, 1, ‘CHECK_OUT’),
(‘2023-10-03 12:45:00’, 3, ‘CHECK_IN’),
(‘2023-10-03 22:00:00’, 2, ‘CHECK_OUT’),
(‘2023-10-05 09:00:00’, 3, ‘CHECK_OUT’),
(‘2023-10-06 16:15:00’, 4, ‘CHECK_IN’);

Теги: #Авито

WITH cte AS (
SELECT
ts,
DATE(ts) AS day,
SUM(CASE WHEN event_type = ‘CHECK_IN’ THEN 1 ELSE -1 END)
OVER (ORDER BY DATE(ts)) AS total,
ROW_NUMBER() OVER (PARTITION BY DATE(ts) ORDER BY ts DESC) AS rn
FROM hotel_log
)
SELECT
ts, day, total
FROM
cte
WHERE
rn = 1
ORDER BY
day

How well did you know this?

Not at all

Perfectly

***понимаю как делать срез. но не групировку

Вывести последний актуальный срез для каждого клиента (name, address), используя оконные функции и группировку. ID инкрементальный

drop table clients;
CREATE TABLE clients (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
address TEXT NOT NULL,
phone TEXT,
transaction_date TIMESTAMP DEFAULT now(),
transaction_amount DECIMAL
);

INSERT INTO clients (name, address, phone, transaction_date, transaction_amount) VALUES
(‘Alice’, ‘123 Main St’, ‘123-456-7890’, NULL, 120),
(‘Alice’, ‘123 Main St’, ‘123-456-7890’, ‘2024-01-10 14:45:00’, NULL),
(‘Alice’, ‘123 Main St’, ‘123-456-7890’, ‘2024-02-05 16:20:00’, 250),
(‘Alice’, ‘123 Main St’, ‘123-456-7890’, NULL, NULL),
(‘Bob’, ‘456 Elm St’, ‘987-654-3210’, ‘2023-11-22 10:15:00’, 180),
(‘Bob’, ‘456 Elm St’, ‘987-654-3210’, ‘2024-02-25 11:30:00’, NULL),
(‘Bob’, ‘456 Elm St’, ‘987-654-3210’, NULL, 220),
(‘Charlie’, ‘789 Oak St’, ‘555-123-4567’, ‘2023-10-03 09:00:00’, 310),
(‘Charlie’, ‘789 Oak St’, ‘555-123-4567’, NULL, 270),
(‘Charlie’, ‘789 Oak St’, ‘555-123-4567’, ‘2024-05-22 16:50:00’, NULL),
(‘David’, ‘321 Pine St’, ‘777-888-9999’, ‘2022-12-30 22:00:00’, 500),
(‘David’, ‘321 Pine St’, ‘777-888-9999’, NULL, 350),
(‘David’, ‘321 Pine St’, ‘777-888-9999’, ‘2023-09-29 19:30:00’, NULL),
(‘Eve’, ‘555 Birch St’, ‘222-333-4444’, ‘2021-07-04 18:45:00’, 150),
(‘Eve’, ‘555 Birch St’, ‘222-333-4444’, NULL, NULL),
(‘Eve’, ‘555 Birch St’, ‘222-333-4444’, ‘2023-05-09 20:30:00’, 275),
(‘Frank’, ‘987 Cedar St’, ‘999-000-1111’, ‘2023-03-12 06:30:00’, NULL),
(‘Frank’, ‘987 Cedar St’, ‘999-000-1111’, NULL, 220),
(‘Frank’, ‘987 Cedar St’, ‘999-000-1111’, ‘2024-02-18 08:40:00’, NULL);
(‘Frank’, ‘987 Cedar St’, ‘999-000-1111’, ‘2024-02-18 08:40:00’, NULL);

Теги: #Yandex

Через групировку:

SELECT
name,
address,
(array_agg(phone ORDER BY id DESC))[1] AS phone,
(array_agg(transaction_date ORDER BY id DESC))[1] AS transaction_date,
EXTRACT(YEAR FROM (array_agg(transaction_date ORDER BY id DESC))[1]) AS year_cool,
EXTRACT(MONTH FROM (array_agg(transaction_date ORDER BY id DESC))[1]) AS month_cool,
SUM(transaction_amount) AS total
FROM clients
GROUP BY
name,
address,
EXTRACT(YEAR FROM transaction_date),
EXTRACT(MONTH FROM transaction_date)
ORDER BY
name,
year_cool,
month_cool;

Через оконку:

WITH ranked_clients AS (
SELECT
id,
name,
address,
phone,
transaction_date,
EXTRACT(YEAR FROM transaction_date) AS year_cool,
EXTRACT(MONTH FROM transaction_date) AS month_cool,
transaction_amount,
SUM(transaction_amount) OVER (
PARTITION BY name, EXTRACT(YEAR FROM transaction_date), EXTRACT(MONTH FROM transaction_date)
) AS total_transactions,
ROW_NUMBER() OVER (
PARTITION BY name, address
ORDER BY id DESC
) AS rn
FROM clients
)
SELECT
id,
name,
address,
phone,
transaction_date,
year_cool,
month_cool,
total_transactions
FROM ranked_clients
WHERE rn = 1
ORDER BY name, year_cool, month_cool;

если без агрегации:

WITH ranked_clients AS (
SELECT
id,
name,
address,
phone,
transaction_date,
ROW_NUMBER() OVER (
PARTITION BY name, address
ORDER BY id DESC
) AS rn
FROM clients
)
SELECT
id,
name,
address,
phone,
transaction_date
FROM ranked_clients
WHERE rn = 1
ORDER BY name, address;

SELECT
name,
address,
(array_agg(id ORDER BY id DESC))[1] AS id,
(array_agg(phone ORDER BY id DESC))[1] AS phone,
(array_agg(transaction_date ORDER BY id DESC))[1] AS transaction_date
FROM clients
GROUP BY name, address
ORDER BY name, address;

1) если будут пропуски в датах
Техника:

Генерация ряда дат с помощью generate_series (или аналогов в других СУБД).
Кросс-присоединение (CROSS JOIN) клиентов и дат для получения полного набора данных.
LEFT JOIN с исходными данными для объединения транзакций с полным рядом дат.
Замена отсутствующих значений на 0 (COALESCE).

Ключевые функции и приёмы:

generate_series(start_date, end_date, interval '1 month') – создаёт последовательность месяцев.
CROSS JOIN – помогает комбинировать клиентов и все возможные даты.
LEFT JOIN – позволяет присоединить реальные данные, сохраняя строки с отсутствующими значениями.
COALESCE(value, default) – заменяет NULL на 0.

2) нужно вычислить сумму не включая текущую транзакцию (только все предыдущие)

Техника:

Оконные функции (SUM() OVER()) для вычисления кумулятивных сумм.
Использование рамок в оконных функциях (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) для исключения текущей строки.

Ключевые функции и приёмы:

SUM(transaction_amount) OVER (PARTITION BY name ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) – суммирует все предыдущие транзакции, исключая текущую.
PARTITION BY – группирует данные по клиенту.
ORDER BY transaction_date – упорядочивает транзакции для правильного вычисления.

3) если будут null в суммах транзакций

Техника:

Использование COALESCE() для замены NULL на 0 в арифметических операциях.
Группировка (GROUP BY) и агрегация (SUM()) с обработкой NULL.

Ключевые функции и приёмы:

COALESCE(transaction_amount, 0) – заменяет NULL на 0 перед суммированием.
SUM(COALESCE(transaction_amount, 0)) – корректно обрабатывает суммы, даже если некоторые значения NULL.

How well did you know this?

Not at all

Perfectly

Определите средний объем транзакций по месяцам за последний год
CREATE TABLE Transactions (TransactionID INT,
TransactionDate DATE, Amount DECIMAL);

INSERT INTO Transactions (TransactionID, TransactionDate, Amount) VALUES
(1, ‘2024-02-15’, 150.00),
(2, ‘2024-02-20’, 200.00),
(3, ‘2023-12-05’, 100.00),
(4, ‘2023-11-25’, 300.00),
(5, ‘2023-10-10’, 250.00),
(6, ‘2023-09-15’, 400.00),
(7, ‘2023-08-20’, 50.00),
(8, ‘2023-07-05’, 125.00),
(9, ‘2023-06-18’, 175.00),
(10, ‘2023-05-12’, 225.00);

Теги: #т1

SELECT
EXTRACT(MONTH FROM TransactionDate) AS month,
AVG(Amount) AS avg_num
FROM
Transactions
WHERE
TransactionDate BETWEEN NOW() - INTERVAL ‘1 YEAR’ AND NOW()
GROUP BY
month

How well did you know this?

Not at all

Perfectly

решил.

2) Вывести суммарный баланс по договорам Зарплат на конец 1 кв. 2024

drop table if exists deals;
create table deals (
id serial primary key,
deal_no varchar(50),
balance numeric(15,2),
report_date date
);

– Вставка тестовых данных
insert into deals (deal_no, balance, report_date) values
(‘D1001’, 5000.00, ‘2024-01-01’),
(‘D1001’, 5200.00, ‘2024-02-01’),
(‘D1001’, 5100.00, ‘2024-03-01’),
(‘D1002’, 7000.00, ‘2024-01-15’),
(‘D1002’, 6900.00, ‘2024-02-15’),
(‘D1003’, 8000.00, ‘2024-03-05’),
(‘D1003’, 8100.00, ‘2024-04-05’),
(‘D1003’, 8200.00, ‘2024-05-05’);

Теги: #т1

WITH cte AS (
SELECT
deal_no, balance, report_date,
ROW_NUMBER() OVER (PARTITION BY deal_no ORDER BY report_date) AS rn
FROM
deals
WHERE
report_date <= ‘2024-03-01’
)
SELECT
SUM(balance)
FROM
cte
WHERE
rn = 1

How well did you know this?

Not at all

Perfectly

SELECT c.Customer, c.State, e.Entry
FROM Customer c
LEFT JOIN Entry e
ON c.Customer = e.Customer AND e.Category = ‘D’

SELECT Customer.Customer, Customer.State, Entry.Entry
FROM Customer
LEFT JOIN Entry
ON Customer.Customer = Entry.Customer

WHERE Entry.Category = ‘D’

в чем разница? #собес

Основное различие между двумя SQL-запросами заключается в порядке применения фильтрации (WHERE) и в логике обработки отсутствующих записей при использовании LEFT JOIN.

Первый запрос

Разъяснение:

Условие e.Category = 'D' включено в ON-условие соединения.
Это означает, что сначала происходит соединение таблиц на основе условия c.Customer = e.Customer и дополнительно фильтрации по e.Category = 'D'.
Если для какого-то клиента не существует записи в таблице Entry с категорией 'D', то запись из Customer всё равно попадёт в результат, но значения из Entry будут NULL.

Ключевая особенность:

В результате будут все записи из Customer, независимо от того, есть ли совпадающие записи в Entry с категорией 'D'. Если таких записей нет, поля из Entry будут заполнены NULL.

Второй запрос

Разъяснение:

Условие Entry.Category = 'D' вынесено в WHERE, а не в ON.
Сначала выполняется соединение всех записей из таблицы Customer с таблицей Entry по условию Customer.Customer = Entry.Customer, а затем результат фильтруется с помощью WHERE Entry.Category = 'D'.
Если для какого-то клиента нет записей с категорией 'D', то из-за условия в WHERE он будет удалён из результата.

Ключевая особенность:

В результате будут только те записи, у которых существует соответствующая запись в Entry с категорией 'D'.
Если таких записей нет, строка из Customer вообще исключается из результата.

How well did you know this?

Not at all

Perfectly

Чем отличаются эти 2 запроса и почему ? #ивитро

1.
SELECT*
FROMt2
LEFTJOINt1
ON t1.id=t2.id
ANDt1.nISnull
2.
SELECT*
FROMt2
LEFTJOINt1
ON t1.id=t2.id
WHEREt1.nISnull

ON t1.n IS NULL → фильтрация происходит во время соединения → строки t2 остаются всегда, но данные из t1 могут быть NULL (если пара не подошла).

WHERE t1.n IS NULL → фильтрация после соединения → строки t2, которые не нашли пару или у которых в t1 n IS NOT NULL, исключаются вообще.

Первый запрос (AND t1.n IS NULL в ON)

Условие t1.n IS NULL применяется только во время соединения.
t2 сохраняет все строки, даже если t1 не заджойнился.
Если t1 не нашлось, в результат попадёт t2 с NULL в t1.

Второй запрос (WHERE t1.n IS NULL)

LEFT JOIN сначала соединяет, затем WHERE фильтрует результат.
	Если у t2 нашлась пара в t1, но t1.n не NULL → строка отфильтруется.
Если у t2 вообще нет пары в t1, то все поля t1 станут NULL → условие t1.n IS NULL выполнится, и такие строки останутся.

How well did you know this?

Not at all

Perfectly

не решил из-за года дважды

Теги: #t1

CREATE TABLE Transactions (
TransactionID SERIAL PRIMARY KEY,
TransactionDate DATE NOT NULL,
Amount DECIMAL(15,2) NOT NULL CHECK (Amount >= 0)
);

INSERT INTO Transactions (TransactionDate, Amount) VALUES
(‘2025-02-15’, 120.50),
(‘2025-02-20’, 75.25),
(‘2025-01-10’, 200.00),
(‘2025-01-25’, 150.75),
(‘2025-12-05’, 300.40),
(‘2023-12-18’, 99.99),
(‘2023-11-12’, 250.60),
(‘2023-11-30’, 180.80),
(‘2023-10-07’, 320.00),
(‘2023-10-22’, 215.25),
(‘2023-09-09’, 400.75),
(‘2023-09-28’, 125.30),
(‘2023-08-14’, 175.90),
(‘2023-08-25’, 290.45),
(‘2025-07-06’, 110.80),
(‘2023-07-21’, 140.65),
(‘2023-06-11’, 310.00),
(‘2023-06-30’, 95.55),
(‘2023-05-15’, 275.75),
(‘2025-05-29’, 225.45),
(‘2023-04-10’, 180.20),
(‘2023-04-27’, 330.85),
(‘2023-03-05’, 420.90),
(‘2025-03-19’, 200.15),
(‘2023-02-22’, 150.40),
(‘2023-02-28’, 175.60),
(‘2023-01-13’, 135.75),
(‘2023-01-24’, 290.80);

SELECT
EXTRACT(YEAR FROM TransactionDate) AS year,
EXTRACT(MONTH FROM TransactionDate) AS month,
ROUND(AVG(Amount), 2)
FROM
Transactions
GROUP BY
year, month
ORDER BY
year, month

How well did you know this?

Not at all

Perfectly

не решил

Рассчитайте среднюю зарплату в каждом отделе и определите–, какие сотрудники получают зарплату выше средней по своему отделу

CREATE TABLE Employees (
EmployeeID SERIAL PRIMARY KEY,
DepartmentID INT NOT NULL,
Salary DECIMAL(15,2) NOT NULL CHECK (Salary > 0)
);

INSERT INTO Employees (DepartmentID, Salary) VALUES
(1, 5000),
(1, 7000),
(1, 6000),
(2, 8000),
(2, 7500),
(2, 9000),
(3, 4000),
(3, 4500),
(3, 4800);

Теги: #t1

WITH AvgSalaries AS (
SELECT
DepartmentID,
AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT
e.EmployeeID,
e.DepartmentID,
e.Salary,
a.AvgSalary
FROM Employees e
JOIN AvgSalaries a ON e.DepartmentID = a.DepartmentID
WHERE e.Salary > a.AvgSalary;

How well did you know this?

Not at all

Perfectly

запутался немного

По каждому договору вывести последнюю запись, колонки - deal_NO, balance, report_date

CREATE TABLE Deals (
deal_NO INT NOT NULL,
balance DECIMAL(15,2) NOT NULL,
report_date DATE NOT NULL,
PRIMARY KEY (deal_NO, report_date) – Уникальная комбинация
);

INSERT INTO Deals (deal_NO, balance, report_date) VALUES
(101, 1000, ‘2024-01-10’),
(101, 1500, ‘2024-02-15’),
(102, 2000, ‘2024-01-20’),
(102, 2500, ‘2024-02-25’),
(103, 3000, ‘2024-02-28’);

Теги; #t1

WITH cte AS (
SELECT
deal_NO,
balance,
report_date,
ROW_NUMBER() OVER(PARTITION BY deal_NO ORDER BY report_date DESC) AS rn
FROM
Deals
)
SELECT
deal_NO, balance, report_date
FROM
cte
WHERE
rn = 1

How well did you know this?

Not at all

Perfectly

Ощибся в логике( ГДЕ ФИЛЬТРУЕМ?)

Вывести суммарный баланс по договорам Зарплатный на конец 1 кв. 2018

CREATE TABLE Contracts (
deal_NO INT NOT NULL,
balance DECIMAL(15,2) NOT NULL,
report_date DATE NOT NULL,
contract_type VARCHAR(50) NOT NULL,
PRIMARY KEY (deal_NO, report_date) – Уникальная комбинация
);

INSERT INTO Contracts (deal_NO, balance, report_date, contract_type) VALUES
(201, 5000, ‘2018-03-30’, ‘Зарплатный’),
(201, 5200, ‘2018-03-31’, ‘Зарплатный’),
(202, 3000, ‘2018-03-31’, ‘Зарплатный’),
(203, 4500, ‘2018-02-15’, ‘Зарплатный’),
(204, 4000, ‘2018-03-20’, ‘Зарплатный’),
(205, 6000, ‘2018-03-31’, ‘Кредитный’); – Не учитываем, так как не “Зарплатный”

WITH cte AS (
SELECT
*,
ROW_NUMBER() OVER(PARTITION BY deal_NO ORDER BY report_date DESC) AS rn
FROM
Contracts
WHERE
report_date <= ‘2018-03-31’ AND contract_type = ‘Зарплатный’
)
SELECT
SUM(balance) AS total_balance
FROM
cte
WHERE
rn = 1

How well did you know this?

Not at all

Perfectly

какая разница в выводе строк между запросами,надо было написать сами запросы и порассуждать
Табл1 Табл2
id id product
1 1 ипотека
2 2
3 3
select table2.product,
from table1 left join table2 on table1.id=table2.id AND table2.product=’Ипотека’

select table2.product,
from table1 left join table2 on table1.id=table2.id
where table2.product=’Ипотека’

теги #t1

Результат запроса 1
product
ипотека
(NULL)
(NULL)

Логика работы:

Выполняется LEFT JOIN таблицы table1 с table2 по id, но с дополнительным условием table2.product = 'Ипотека' в ON.
Это означает, что строка из table1 соединится с table2, только если product = 'Ипотека'.
Если product не соответствует этому условию или в table2 нет соответствующего id, то для table2.product в результате будет NULL.

Результат запроса 2
product
ипотека

Логика работы:

Сначала выполняется LEFT JOIN, который присоединяет table2 к table1 по id (без фильтрации product на этом этапе).
Затем уже на уровне WHERE отфильтровываются только те строки, где table2.product = 'Ипотека'.
Если в table2.product NULL, строка будет исключена WHERE-фильтром.

How well did you know this?

Not at all

Perfectly

Не решил. забыл логику

таблица со временем, надо найти пересечения (1 решение – ток с друг другом ) (второе – все)
ID Date_start Date_end
1 11 13
2 13 14
3 15 16

CREATE TABLE TimeRanges (
ID SERIAL PRIMARY KEY,
Date_start INT NOT NULL,
Date_end INT NOT NULL CHECK (Date_end > Date_start)
);

INSERT INTO TimeRanges (Date_start, Date_end) VALUES
(11, 13),
(13, 14),
(15, 16);

теги #t1

все (даже сам с собой)

SELECT *
FROM TimeRanges t1
JOIN TimeRanges t2
ON t2.Date_end BETWEEN t1.Date_start AND t1.Date_end

ток с друг другом

SELECT *
FROM TimeRanges t1
JOIN TimeRanges t2
ON t1.ID <> t2.ID
AND t1.Date_start <= t2.Date_end
AND t1.Date_end >= t2.Date_start;

How well did you know this?

Not at all

Perfectly

решил но невнимательность.

Дана таблица org с полями
name - наименование организации
volume_sales - объем продаж, шт.
Необходимо написать запрос который возвращает список организаций, с указанием объема продаж, состоящий из топ 5 организаций
по объему продаж (от большего к меньшему) + одна строка с объемом продаж организаций не попавших в топ 5.

CREATE TABLE org (
name TEXT,
volume_sales INT
);

INSERT INTO org (name, volume_sales) VALUES
(‘Компания А’, 1000),
(‘Компания B’, 800),
(‘Компания C’, 750),
(‘Компания D’, 600),
(‘Компания E’, 500),
(‘Компания D’, 400),
(‘Компания А’, 350),
(‘Компания D’, 900),
(‘Компания R’, 100),
(‘Компания R’, 100),
(‘Компания Z’, 700),
(‘Компания C’, 300);

теги #Ретейлдата

не решил

WITH ranked_orgs AS (
SELECT name, SUM(volume_sales) AS total_sales
FROM org
GROUP BY name
), top_orgs AS (
SELECT name, total_sales
FROM ranked_orgs
ORDER BY total_sales DESC
LIMIT 5
), others AS (
SELECT ‘Остальные’ AS name, COALESCE(SUM(total_sales), 0) AS total_sales
FROM ranked_orgs
WHERE name NOT IN (SELECT name FROM top_orgs)
)
SELECT * FROM top_orgs
UNION ALL
SELECT * FROM others
ORDER BY total_sales DESC;

How well did you know this?

Not at all

Perfectly

не решил . сложно

Дана таблица “Календарь” с полями
date - дата
wow - признак выходного или рабочего дня

Написать запрос, который будет возвращать исходную таблицу + колонку с ближайшим следующим рабочим днем, если текущий день выходной.

Календарь
date wow
2018-07-13 Р
2018-07-14 В
2018-07-15 В
2018-07-16 Р
2018-07-17 Р
2018-07-18 Р
2018-07-19 Р
2018-07-20 Р
2018-07-21 В
2018-07-22 В
2018-07-23 Р
2018-07-24 Р\
пример результата
date wow new_date
2018-07-13 Р 2018-07-13
2018-07-14 В 2018-07-16
2018-07-15 В 2018-07-16
2018-07-16 Р 2018-07-16

CREATE TABLE calendar (
date DATE PRIMARY KEY,
wow CHAR(1) CHECK (wow IN (‘Р’, ‘В’))
);

INSERT INTO calendar (date, wow) VALUES
(‘2018-07-13’, ‘Р’),
(‘2018-07-14’, ‘В’),
(‘2018-07-15’, ‘В’),
(‘2018-07-16’, ‘Р’),
(‘2018-07-17’, ‘Р’),
(‘2018-07-18’, ‘Р’),
(‘2018-07-19’, ‘Р’),
(‘2018-07-20’, ‘Р’),
(‘2018-07-21’, ‘В’),
(‘2018-07-22’, ‘В’),
(‘2018-07-23’, ‘Р’),
(‘2018-07-24’, ‘Р’);

теги #Ретейлдата

SELECT
date,
wow,
CASE
WHEN wow = ‘В’ THEN (
MIN(date) FILTER (WHERE wow = ‘Р’)
OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
)
ELSE NULL
END AS next
FROM calendar;

How well did you know this?

Not at all

Perfectly

**не решил дважды. сложно забыл как сессии делать и забыл в коцне **

Представим таблицу событий пользователей в системе. Каждая запись в таблице содержит идентификатор пользователя, метку времени события и тип события.

– Создание таблицы
CREATE TABLE public.events (
user_id INT,
event_ts TIMESTAMP,
event TEXT
);

– Вставка тестовых данных
INSERT INTO public.events (user_id, event_ts, event) VALUES
(1, ‘2023-01-01 12:00:00’, ‘login’),
(1, ‘2023-01-01 12:05:00’, ‘view_page’),
(1, ‘2023-01-01 12:30:00’, ‘logout’),
(2, ‘2023-01-01 13:00:00’, ‘login’),
(2, ‘2023-01-01 13:13:00’, ‘view_page’),
(2, ‘2023-01-01 13:25:00’, ‘purchase’),
(3, ‘2023-01-02 08:00:00’, ‘login’),
(3, ‘2023-01-02 08:45:00’, ‘view_page’),
(3, ‘2023-01-02 08:50:00’, ‘logout’),
(4, ‘2023-01-02 09:00:00’, ‘login’),
(4, ‘2023-01-02 09:10:00’, ‘view_page’),
(4, ‘2023-01-02 09:11:00’, ‘purchase’),
(4, ‘2023-01-02 09:25:00’, ‘logout’),
(5, ‘2023-01-03 10:05:00’, ‘login’),
(5, ‘2023-01-03 10:10:00’, ‘view_page’),
(5, ‘2023-01-03 10:20:00’, ‘view_page’),
(5, ‘2023-01-03 10:40:00’, ‘logout’),
(6, ‘2023-01-03 11:00:00’, ‘login’),
(6, ‘2023-01-03 11:05:00’, ‘view_page’),
(6, ‘2023-01-03 11:30:00’, ‘purchase’),
(6, ‘2023-01-03 11:45:00’, ‘logout’),
(7, ‘2023-01-04 14:00:00’, ‘login’),
(7, ‘2023-01-04 14:10:00’, ‘add_to_cart’),
(7, ‘2023-01-04 14:15:00’, ‘purchase’),
(7, ‘2023-01-04 14:25:00’, ‘logout’),
(8, ‘2023-01-05 16:00:00’, ‘login’),
(8, ‘2023-01-05 16:15:00’, ‘view_page’),
(8, ‘2023-01-05 16:30:00’, ‘view_page’),
(8, ‘2023-01-05 16:50:00’, ‘logout’);

/*
user_id event_ts event
1 2023-01-01 12:00:00 login
1 2023-01-01 12:05:00 view_page
1 2023-01-01 12:30:00 logout
2 2023-01-01 13:00:00 login
2 2023-01-01 13:13:00 view_page
2 2023-01-01 13:25:00 purchase

/
– 1) Наша задача — агрегировать эти события в ‘сессии’. Сессией будем считать последовательность событий одного пользователя, между которыми не проходит более 15 минут. Нужно определить начало и конец каждой сессии. То есть результат должен получиться :
/
user_id session_start session_end
1 2023-01-01 12:00:00 2023-01-01 12:05:00
1 2023-01-01 12:30:00 2023-01-01 12:30:00
2 2023-01-01 13:00:00 2023-01-01 13:25:00
*/

– 2) добавить список событий сессии (события должны быть упорядочены)
/*
user_id,session_start session_end events
1 2023-01-01 12:00:00 2023-01-01 12:05:00 [login, view_page]
1 2023-01-01 12:30:00 2023-01-01 12:30:00 [logout]
2 2023-01-01 13:00:00 2023-01-01 13:25:00 [login,view_page, purchase]
*/

Теги wildberries

WITH sessionisation AS (
SELECT
user_id,
event_ts,
event,
CASE
WHEN EXTRACT(EPOCH FROM (event_ts - LAG(event_ts)
OVER (PARTITION BY user_id ORDER BY event_ts))) > 900
OR LAG(event_ts) OVER (PARTITION BY user_id ORDER BY event_ts) IS NULL
THEN 1
ELSE 0
END AS flag
FROM public.events
),
ses_grouping AS (
SELECT
user_id,
event_ts,
event,
SUM(flag) OVER (PARTITION BY user_id ORDER BY event_ts) AS group_flag
FROM sessionisation
)
SELECT
user_id,
MIN(event_ts) AS session_start,
MAX(event_ts) AS session_end,
– Собираем упорядоченный массив событий
array_agg(event ORDER BY event_ts) AS events
FROM ses_grouping
GROUP BY
user_id,
group_flag
ORDER BY
user_id,
session_start;

How well did you know this?

Not at all

Perfectly

Дана таблица t, в ней одно поле value. нужно вывести все дубли.

CREATE TABLE t (
value INT
);

INSERT INTO t (value) VALUES
(1), (1), (1), (2), (3), (4), (5), (5), (6), (1), (5);

Теги #ЛигаЦифровогоИнтернета

без повторов

SELECT value
FROM t
GROUP BY value
HAVING COUNT(*) > 1;

с повторовов

WITH dup AS (
SELECT
value
FROM
t
GROUP BY
value
HAVING COUNT(*) > 1
)
SELECT
value
FROM
t
WHERE
value IN (SELECT value FROM dup)

How well did you know this?

Not at all

Perfectly

Решил. Но что делать в случае если сотрудник один?

Вывести сотрудника и департамент со второй максимальной зарплатой в каждом департаменте за парель 2023 года.

CREATE TABLE employees (
employee_id INT,
department_id INT,
salary NUMERIC,
salary_date DATE
);

INSERT INTO employees (employee_id, department_id, salary, salary_date) VALUES
(1, 101, 50000, ‘2023-04-01’),
(2, 101, 70000, ‘2023-04-01’),
(3, 101, 80000, ‘2023-04-01’),
(4, 102, 55000, ‘2023-04-01’),
(5, 102, 75000, ‘2023-04-01’),
(6, 102, 85000, ‘2023-04-01’),
(7, 103, 60000, ‘2023-04-01’),
(8, 103, 72000, ‘2023-04-01’),
(9, 103, 88000, ‘2023-04-01’);

Теги #ЛигаЦифровогоИнтернета

WITH cte AS (
SELECT
employee_id, department_id, salary, salary_date,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rn
FROM
employees
)
SELECT
employee_id, department_id, salary, salary_date
FROM
cte
WHERE rn = 2

How well did you know this?

Not at all

Perfectly

**Не решил **

Есть две таблицы с приходами и списаниями с банковских счетов:
Income
account_id datetime amount
Outcome
account_id datetime amount
Необходимо написать запрос, который вернет баланс выбранного счета на каждый день когда были операции выбранного месяца. (в рамках задачи id только 777 и месяц февраль 2024

CREATE TABLE income (
account_id INT,
datetime TIMESTAMP,
amount NUMERIC
);

INSERT INTO income (account_id, datetime, amount) VALUES
(777, ‘2024-02-01 10:00:00’, 1000),
(777, ‘2024-02-05 14:00:00’, 500),
(777, ‘2024-02-10 16:30:00’, 2000),
(555, ‘2024-02-02 09:00:00’, 300), – другой счет
(777, ‘2024-03-01 12:00:00’, 400), – другой месяц
(888, ‘2024-02-10 15:00:00’, 600); – другой счет

CREATE TABLE outcome (
account_id INT,
datetime TIMESTAMP,
amount NUMERIC
);

INSERT INTO outcome (account_id, datetime, amount) VALUES
(777, ‘2024-02-03 11:00:00’, 300),
(777, ‘2024-02-07 12:45:00’, 700),
(777, ‘2024-02-12 18:15:00’, 1500),
(555, ‘2024-02-04 08:30:00’, 200), – другой счет
(777, ‘2024-03-02 17:00:00’, 500), – другой месяц
(888, ‘2024-02-15 10:30:00’, 900); – другой счет

Теги #ЛигаЦифровогоИнтернета

WITH all_transactions AS (
SELECT account_id, datetime, amount, ‘income’ AS source
FROM income
WHERE account_id = 777 AND DATE_TRUNC(‘month’, datetime) = ‘2024-02-01’

UNION ALL

SELECT account_id, datetime, amount, 'outcome' AS source 
FROM outcome
WHERE account_id = 777 AND DATE_TRUNC('month', datetime) = '2024-02-01' )

SELECT
datetime,
EXTRACT(MONTH FROM datetime) AS month,
EXTRACT(DAY FROM datetime) AS day,
SUM(CASE WHEN source = ‘income’ THEN amount ELSE -amount END)
OVER (ORDER BY datetime) AS balance
FROM all_transactions
ORDER BY datetime, month, day

не решил

Список сотрудников, которые старше своего непосредственного руководителя

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name TEXT,
birth_date DATE,
manager_id INT
);

INSERT INTO employees (employee_id, name, birth_date, manager_id) VALUES
(1, ‘Иван Иванов’, ‘1985-06-15’, NULL), – Руководитель
(2, ‘Петр Петров’, ‘1990-04-20’, 1), – Подчиненный
(3, ‘Анна Смирнова’, ‘1983-09-10’, 1), – Подчиненный старше руководителя
(4, ‘Олег Сидоров’, ‘1995-07-25’, 2), – Подчиненный
(5, ‘Мария Кузнецова’, ‘1987-12-05’, 2);– Подчиненный

Теги #т-банк

Первая успешная транзакция для каждого клиента, у которого общая сумма успешных транзакций > 100000 CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, client_id INT, transaction_date TIMESTAMP, amount NUMERIC, status TEXT CHECK (status IN ('success', 'failed')) ); INSERT INTO transactions (client_id, transaction_date, amount, status) VALUES (1, '2024-02-01 10:00:00', 50000, 'success'), (1, '2024-02-05 12:00:00', 60000, 'success'), (1, '2024-02-10 14:30:00', 20000, 'failed'), (2, '2024-02-03 09:15:00', 30000, 'success'), (2, '2024-02-08 11:45:00', 80000, 'success'), (2, '2024-02-15 17:30:00', 15000, 'success'), (3, '2024-02-02 08:00:00', 120000, 'success'), (3, '2024-02-06 10:30:00', 50000, 'success'); Теги #т-банк

Транзакции, сумма которых больше, чем у предыдущей и последующей транзакции по каждому клиенту CREATE TABLE transactions_peak ( transaction_id SERIAL PRIMARY KEY, client_id INT, transaction_date TIMESTAMP, amount NUMERIC ); INSERT INTO transactions_peak (client_id, transaction_date, amount) VALUES (1, '2024-02-01 10:00:00', 50000), (1, '2024-02-02 11:30:00', 100000), -- Пик (1, '2024-02-03 15:00:00', 60000), (2, '2024-02-05 09:45:00', 20000), (2, '2024-02-06 12:15:00', 70000), -- Пик (2, '2024-02-07 18:45:00', 50000), (3, '2024-02-08 10:30:00', 90000), (3, '2024-02-09 14:00:00', 150000), -- Пик (3, '2024-02-10 16:30:00', 80000); Теги #т-банк

Даны 2 таблицы: Tab1: --- A 1 1 2 2 3 null null Tab2: ---- A 1 1 2 4 null как будет выглядеть таблица при запросах: select * from Tab1 full join Tab2 on Tab1.A = Tab2.A; select * from Tab1 left join Tab2 on Tab1.A = Tab2.A; Теги #glowbyte

Tab1.A Tab2.A Tab1 (Integer) Tab2 (Integer) 1 1 1 1 1 1 1 1 2 2 2 2 3 [null] [null] [null] [null] [null] [null] 4 [null] [null] 2 ) Tab1 (Integer) Tab2 (Integer) 1 1 1 1 1 1 1 1 2 2 2 2 3 [null] [null] [null] [null] [null]

# ***Не решил*** Исходная таблица (clients) client_id param_name param_value 1 Name Иванов 1 Age 33 2 Name Петров Нужно получить: client_id client_name client_age 1 Иванов 33 2 Петров NULL CREATE TABLE clients ( client_id INTEGER, param_name TEXT, param_value TEXT ); INSERT INTO clients (client_id, param_name, param_value) VALUES (1, 'Name', 'Иванов'), (1, 'Age', '33'), (2, 'Name', 'Петров'); Теги #glowbyte

ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ ответ SELECT client_id, MAX(CASE WHEN param_name = 'Name' THEN param_value END) AS client_name, MAX(CASE WHEN param_name = 'Age' THEN param_value END)::int AS client_age FROM clients GROUP BY client_id ORDER BY client_id;

# **Удалил еще и не допубликат** написать select-запрос, который удаляет дубликаты. как эти дубликаты отличаются не важно CREATE TABLE duplicates_table ( id SERIAL PRIMARY KEY, value TEXT ); INSERT INTO duplicates_table (value) VALUES ('A'), ('B'), ('A'), ('C'), ('D'), ('B'), ('E'), ('F'), ('C'), ('C'); Теги #glowbyte

# забыл как это сделать релаьно написать запрос, который реально удалит эти строки (дубликаты) в таблице CREATE TABLE duplicates_removal ( id SERIAL PRIMARY KEY, value TEXT ); INSERT INTO duplicates_removal (value) VALUES ('A'), ('B'), ('A'), ('C'), ('D'), ('B'), ('E'), ('F'), ('C'), ('C'); Теги #glowbyte

# ***Не решил*** Есть таблица Заказы (orders): CREATE TABLE orders ( order_id int, customer_id int, order_status varchar, valid_from date, valid_to date ); Есть таблица маппинга клиентов и заказов (orders2customers): CREATE TABLE orders2customers ( order_id int, customer_id int, external_customer_flg bool ); Необходимо обновить поле customer_id в таблице orders на основе orders2customers только у актуальных заказов и только по внешним клиентам. 2. Добавляется таблица актуальных заказов (orders_current): CREATE TABLE orders_current ( order_id int, order_status varchar ); Было в orders: 1, 12, 'New', 2024-11-10, 2099-12-31 2, 13, 'Pending', 2024-10-10 2099-12-31 Orders_current: 1, 'Finished' 2, 'Pending' 3, 'New' Должно стать в orders: 1, 12, 'New', 2024-11-10, 2024-11-10 1, 12, 'Finished', 2024-11-11 2099-12-31 2, 13, 'Pending', 2024-10-10 2099-12-31 3, NULL, 'New', 2024-11-11 2099-12-31 Обновить таблицу orders на основе orders_current и orders2customers. 1. Если заказа из orders_current нет в orders, добавить его (valid_from = current_date, valid_to = 2099-12-31). 2. Если статус заказа поменялся, закрыть предыдущую запись (valid_to = current_date - 1), добавить новую запись (valid_from = current_date, valid_to = 2099-12-31). 3. Если заказа из orders нет в orders_current, ничего не делать.-- tckb CREATE TABLE orders ( order_id INT, customer_id INT, order_status VARCHAR, valid_from DATE, valid_to DATE ); INSERT INTO orders (order_id, customer_id, order_status, valid_from, valid_to) VALUES (1, 12, 'New', '2024-11-10', '2099-12-31'), (2, 13, 'Pending', '2024-10-10', '2099-12-31'); CREATE TABLE orders2customers ( order_id INT, customer_id INT, external_customer_flg BOOL ); INSERT INTO orders2customers (order_id, customer_id, external_customer_flg) VALUES (1, 99, TRUE), (2, 88, FALSE), (3, 77, TRUE); CREATE TABLE orders_current ( order_id INT, order_status VARCHAR ); INSERT INTO orders_current (order_id, order_status) VALUES (1, 'Finished'), (2, 'Pending'), (3, 'New'); Теги #glowbyte

# ***не решил дважды!!!*** Top10 водителей по количеству заказов в каждом городе CREATE TABLE drivers ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE cities ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, driver_id INT REFERENCES drivers(id), city_id INT REFERENCES cities(id), order_date TIMESTAMP ); INSERT INTO drivers (name) VALUES ('Иванов Иван'), ('Петров Петр'), ('Сидоров Сидор'); INSERT INTO cities (name) VALUES ('Москва'), ('Санкт-Петербург'), ('Казань'); INSERT INTO orders (driver_id, city_id, order_date) VALUES (1, 1, '2024-01-01 12:00:00'), (1, 1, '2024-01-02 13:00:00'), (2, 2, '2024-01-01 14:00:00'), (2, 2, '2024-01-03 15:00:00'), (3, 3, '2024-01-01 16:00:00'); Теги #yandex

# ***не решил*** Протяжка логов(id, ts, value) Необходимо заполнить NULLы предыдущим значением (не NULL) - протяжка вниз, т.е. получить таблицу CREATE TABLE logs ( id SERIAL PRIMARY KEY, ts TIMESTAMP, value INT ); INSERT INTO logs (ts, value) VALUES ('2024-01-01 12:00:00', 10), ('2024-01-01 12:10:00', NULL), ('2024-01-01 12:20:00', 15), ('2024-01-01 12:30:00', NULL), ('2024-01-01 12:40:00', NULL), ('2024-01-01 12:50:00', 20); Теги #yandex

Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ WITH groups AS ( SELECT id, ts, value, -- При каждом ненулевом значении счётчик увеличивается, -- что позволяет разбить последовательность на группы. count(value) OVER (ORDER BY ts) AS grp FROM logs ) SELECT id, ts, -- Если значение NULL, то берём первое ненулевое значение в группе, -- что совпадает с последним ненулевым значением на текущий момент. COALESCE(value, first_value(value) OVER (PARTITION BY grp ORDER BY ts)) AS filled_value FROM groups ORDER BY ts;

# ***еле реишл первое и то с миллионом запросов *** дебит с кредитом две таблицы id, data, anmt один с доходом другой с расходом, вывести сальдо потом он усложнил немного добавив таблицу отдельно акаунтов с id и хотел чтобы я вывела его cross join с generate_series('2023-10-01', '2025-10-10', interval '1 day') и добавила сальдо, а если его нет то 0 CREATE TABLE income ( id SERIAL PRIMARY KEY, data DATE, anmt NUMERIC ); CREATE TABLE expense ( id SERIAL PRIMARY KEY, data DATE, anmt NUMERIC ); INSERT INTO income (data, anmt) VALUES ('2024-01-01', 1000), ('2024-01-02', 2000), ('2024-01-03', 1500); INSERT INTO expense (data, anmt) VALUES ('2024-01-01', 500), ('2024-01-02', 1200), ('2024-01-04', 800); усложенная CREATE TABLE accounts ( id SERIAL PRIMARY KEY, name TEXT ); CREATE TABLE transactions ( id SERIAL PRIMARY KEY, account_id INT REFERENCES accounts(id), data DATE, anmt NUMERIC ); INSERT INTO accounts (name) VALUES ('Аккаунт 1'), ('Аккаунт 2'), ('Аккаунт 3'); INSERT INTO transactions (account_id, data, anmt) VALUES (1, '2024-01-01', 1000), (1, '2024-01-02', -500), (2, '2024-01-01', 2000), (2, '2024-01-03', -1500), (3, '2024-01-02', 300); Теги #yandex

# ***не решил*** Вывести клиентов без успешных транзакций CREATE TABLE customer ( customer_id SERIAL PRIMARY KEY, -- ID клиента name VARCHAR(100), -- Имя клиента start_dttm TIMESTAMP WITHOUT TIME ZONE, -- Дата подключения клиента birth_dt DATE -- Дата рождения ); CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, -- ID транзакции customer_id INT REFERENCES customer(customer_id), -- ID клиента amount_rur NUMERIC(10,2), -- Сумма транзакции success_flg BOOLEAN, -- Флаг успешности транзакции transaction_dttm TIMESTAMP WITHOUT TIME ZONE -- Дата и время транзакции ); INSERT INTO customer (name, start_dttm, birth_dt) VALUES ('Иван Иванов', '2023-01-01 10:00:00', '1990-05-10'), ('Петр Петров', '2023-02-15 14:30:00', '1985-07-22'), ('Анна Смирнова', '2023-03-10 08:45:00', '1992-09-05'), ('Сергей Кузнецов', '2023-04-05 16:20:00', '1988-12-30'); INSERT INTO transactions (customer_id, amount_rur, success_flg, transaction_dttm) VALUES (1, 1500.00, TRUE, '2024-01-01 12:00:00'), (1, 2000.00, FALSE, '2024-01-02 15:30:00'), (2, 500.00, FALSE, '2024-01-03 11:20:00'), (2, 700.00, FALSE, '2024-01-04 18:10:00'), (3, 1200.00, TRUE, '2024-01-05 09:50:00'), (3, 800.00, FALSE, '2024-01-06 13:15:00'), (4, 1000.00, FALSE, '2024-01-07 17:40:00'); -- Сергей Кузнецов не имеет успешных транзакций теги #т-банк

# ***не решил*** Имеем таблицу RIDE с поездками пользователей на такси ride_id | user_id | tariff | dt | cost ---------+---------+-------------+------------+------ 2305001 | 1001 | econom | 2023-05-02 | 340 2305002 | 1002 | comfort | 2023-05-03 | 500 2305003 | 2001 | econom | 2023-05-01 | 120 ... Мы хотим, чтобы пассажиры чаще ездили в тарифе comfort, и решили провести промо-рассылку на часть пользователей, которые много ездят в econom, но мало в comfort. Напишите запрос, который найдет всех пользователей, кто за предыдущий месяц суммарно потратил в тарифе econom более 5000, но при этом совершил не более одной поездки (или ноль поездок) в тарифе comfort. CREATE TABLE ride ( ride_id SERIAL PRIMARY KEY, -- ID поездки user_id INT, -- ID пользователя tariff TEXT, -- Тариф (econom, comfort и т.д.) dt DATE, -- Дата поездки cost NUMERIC(10,2) -- Стоимость поездки ); INSERT INTO ride (user_id, tariff, dt, cost) VALUES (1001, 'econom', '2023-04-01', 3000), (1001, 'econom', '2023-04-10', 2500), (1001, 'comfort', '2023-04-15', 400), (1002, 'econom', '2023-04-05', 1000), (1002, 'econom', '2023-04-20', 1200), (1003, 'econom', '2023-04-03', 6000), (1003, 'comfort', '2023-04-18', 500), (1004, 'econom', '2023-04-12', 7000), (1004, 'comfort', '2023-04-28', 0), -- Один пользователь вообще не ездил в comfort (2001, 'econom', '2023-04-08', 5500), (2002, 'econom', '2023-04-10', 5300), (2002, 'comfort', '2023-04-25', 400); Теги #yandex

# ***не решил*** Есть таблица events, в ней содержатся действия пользователей в приложении. Каждое событие относится к какой-то группе (например, действия на карте города, история заказов, просмотр сторис и т.д.). event_id | user_id | grp | dttm | ... ----------+---------+---------+---------------------+---- 26388117 | 1001 | map | 2023-08-19 15:20:04 | ... 26389660 | 1001 | map | 2023-08-19 15:20:18 | 26391788 | 1001 | stories | 2023-08-20 21:03:50 | 26410925 | 2001 | history | 2023-08-10 18:35:30 | ... Пользовательская сессия - это набор подряд идущих событий без длинных пауз между соседними событиями. Если прошло хотя бы 30 минут с момента предыдущего события, то считаем, что это уже начало новой пользовательской сессии. Найти для каждого пользователя общее количество пользовательских сессий, и количество сессий с просмотром сторис (т.е. внутри которых было хотя бы одно событие с grp = 'stories'). CREATE TABLE events ( event_id SERIAL PRIMARY KEY, -- ID события user_id INT, -- ID пользователя grp TEXT, -- Группа событий (map, history, stories и т.д.) dttm TIMESTAMP -- Дата и время события ); INSERT INTO events (user_id, grp, dttm) VALUES (1001, 'map', '2023-08-19 15:20:04'), (1001, 'map', '2023-08-19 15:20:18'), (1001, 'stories', '2023-08-20 21:03:50'), (1001, 'map', '2023-08-20 22:00:00'), (1002, 'history', '2023-08-10 18:35:30'), (1002, 'stories', '2023-08-10 19:00:00'), (1002, 'stories', '2023-08-10 19:10:00'), (1002, 'map', '2023-08-11 10:00:00'), (2001, 'history', '2023-08-09 12:10:00'), (2001, 'map', '2023-08-09 12:50:00'), (2001, 'map', '2023-08-09 13:40:00'), (2001, 'stories', '2023-08-10 14:50:00'), (3001, 'map', '2023-08-15 10:00:00'), (3001, 'map', '2023-08-15 10:25:00'), (3001, 'stories', '2023-08-15 11:00:00'), (3001, 'map', '2023-08-15 12:00:00'), (3001, 'stories', '2023-08-15 12:20:00'); Теги #yandex

Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ Ответ WITH sessionized AS ( SELECT user_id, grp, dttm, -- флаг начала новой сессии: либо первое событие, либо разрыв > 30 минут CASE WHEN LAG(dttm) OVER (PARTITION BY user_id ORDER BY dttm) IS NULL OR dttm - LAG(dttm) OVER (PARTITION BY user_id ORDER BY dttm) > interval '30 minutes' THEN 1 ELSE 0 END AS is_new_session FROM events ), sessions AS ( SELECT user_id, grp, dttm, -- номер сессии внутри пользователя (накопительный сумматор флага) SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY dttm) AS session_id FROM sessionized ), sessions_flags AS ( -- в каждой сессии определяем, была ли хотя бы одна запись grp = 'stories' SELECT user_id, session_id, MAX(CASE WHEN grp = 'stories' THEN 1 ELSE 0 END) AS has_stories FROM sessions GROUP BY user_id, session_id ) SELECT user_id, COUNT(*) AS total_sessions, -- всего сессий SUM(has_stories) AS sessions_with_stories -- сессий, где была stories FROM sessions_flags GROUP BY user_id ORDER BY user_id;

# не решил -- Есть таблицы projects и statuses: select count(status_name) from projects -- выдаст: 10 000 select count(status_name) from statuses -- выдаст: 43 select count(status_name) from projects where status_name = 'hold' -- выдаст 1200 select count(status_name) from statuses where status_name = 'hold' -- выдаст 0 -- Вопрос. -- Запрос select count(0) from projects as p where p.status_name not in (select status_name from statuses) -- выдал 0. -- Почему? Теги #альфа

Запрос вернул 0, потому что подзапрос select status_name from statuses содержит NULL, и оператор NOT IN при наличии хотя бы одного NULL возвращает UNKNOWN для всех сравнений, в результате чего фильтр не пропускает ни одной строки.

a, b, d 2, 3, -5 2, 3, 5 3, 3, 5 --запрос select a, sum(b) from t group by a having sum(b) > 0 and count(3) + a > 0 --Что выведет? Почему? теги #альфа

2 6 3 3

# ***не пон как что делать с дубликатами*** Нужно собрать «базу рынка» (уникальные ИНН) из трёх таблиц (Клиенты, Проспекты, Spark), где один ИНН может встречаться во всех трёх. Как быть с дубликатами и статусами (Client, Prospect, Market)?» CREATE TABLE clients ( inn VARCHAR(12) PRIMARY KEY, -- ИНН клиента (уникальный) name TEXT -- Название компании ); CREATE TABLE prospects ( inn VARCHAR(12) PRIMARY KEY, -- ИНН потенциального клиента (уникальный) name TEXT -- Название компании ); CREATE TABLE spark ( inn VARCHAR(12) PRIMARY KEY, -- ИНН из базы Spark (уникальный) name TEXT -- Название компании ); INSERT INTO clients (inn, name) VALUES ('1234567890', 'ООО Клиент 1'), ('2233445566', 'ООО Клиент 2'), ('3344556677', 'ООО Клиент 3'); INSERT INTO prospects (inn, name) VALUES ('2233445566', 'ООО Клиент 2'), -- Совпадает с Клиентом ('4455667788', 'ООО Проспект 1'), ('5566778899', 'ООО Проспект 2'); INSERT INTO spark (inn, name) VALUES ('5566778899', 'ООО Проспект 2'), -- Совпадает с Проспектом ('6677889900', 'ООО Компания 1'), ('7788990011', 'ООО Компания 2'); теги #альфа

# ***Не с первого раза*** Создать запрос без использования оконных функций, таких как ROW_NUMBER(), который: Выведет таблицу tab и поле с порядковым номером. Отсортирует данные от большего к меньшему. CREATE TABLE tab ( id SERIAL PRIMARY KEY, -- Уникальный ID (порядковый номер) transfer_date DATE -- Дата транзакции ); INSERT INTO tab (transfer_date) VALUES ('2024-10-01'), ('2024-10-02'), ('2024-10-03'), ('2024-10-04'), ('2024-10-05'); теги #Астон

ID бронирования Дата начала бронирования Дата окончания бронирования 1 2023-03-01 2023-03-05 2 2023-03-03 2023-03-07 3 2023-03-06 2023-03-10 Выбрать ID бронирований, которые пересекаются по времени с другими бронированиями. CREATE TABLE bookings ( id SERIAL PRIMARY KEY, -- ID бронирования start_date DATE, -- Дата начала бронирования end_date DATE -- Дата окончания бронирования ); INSERT INTO bookings (start_date, end_date) VALUES ('2023-03-01', '2023-03-05'), ('2023-03-03', '2023-03-07'), ('2023-03-06', '2023-03-10'); Астон

-- Вывести имена читателей, у которых количество книг с ценой выше 3000 рублей составляет больше половины от общего количества их книг. CREATE TABLE readers ( reader_id SERIAL PRIMARY KEY, -- ID читателя name TEXT -- Имя читателя ); CREATE TABLE books ( book_id SERIAL PRIMARY KEY, -- ID книги reader_id INT REFERENCES readers(reader_id), -- ID читателя (кому принадлежит книга) price NUMERIC(10,2) -- Цена книги ); INSERT INTO readers (name) VALUES ('Иван'), ('Мария'), ('Алексей'); INSERT INTO books (reader_id, price) VALUES (1, 2500), (1, 3500), (1, 4000), -- Иван (2 из 3 книг > 3000) (2, 5000), (2, 1000), -- Мария (1 из 2 книг > 3000) (3, 2000), (3, 2500), (3, 2700); -- Алексей (0 книг > 3000) теги Астон

Даны таблицы с параметрами объявлений (адрес и цена) с историчностью по одной колонке. Необходимо вывести актуальные адрес и цену для каждого объявления. create table s_item_address ( item_id int, address varchar(100), actual_date timestamp ); create table s_item_price ( item_id int, price int, actual_date timestamp ); пример что должно получится: lesnay 1 300 1 hhhhh 2 400 1 lesnaw 2 400 2

Вывести последний актуальный срез, но если у клиента есть пропуски в transaction_date, их нужно заполнить. drop table clients; CREATE TABLE clients ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL, phone TEXT, transaction_date TIMESTAMP DEFAULT now(), transaction_amount DECIMAL ); INSERT INTO clients (name, address, phone, transaction_date, transaction_amount) VALUES ('Alice', '123 Main St', '123-456-7890', NULL, 120), ('Alice', '123 Main St', '123-456-7890', '2024-01-10 14:45:00', NULL), ('Alice', '123 Main St', '123-456-7890', '2024-02-05 16:20:00', 250), ('Alice', '123 Main St', '123-456-7890', NULL, NULL), ('Bob', '456 Elm St', '987-654-3210', '2023-11-22 10:15:00', 180), ('Bob', '456 Elm St', '987-654-3210', '2024-02-25 11:30:00', NULL), ('Bob', '456 Elm St', '987-654-3210', NULL, 220), ('Charlie', '789 Oak St', '555-123-4567', '2023-10-03 09:00:00', 310), ('Charlie', '789 Oak St', '555-123-4567', NULL, 270), ('Charlie', '789 Oak St', '555-123-4567', '2024-05-22 16:50:00', NULL), ('David', '321 Pine St', '777-888-9999', '2022-12-30 22:00:00', 500), ('David', '321 Pine St', '777-888-9999', NULL, 350), ('David', '321 Pine St', '777-888-9999', '2023-09-29 19:30:00', NULL), ('Eve', '555 Birch St', '222-333-4444', '2021-07-04 18:45:00', 150), ('Eve', '555 Birch St', '222-333-4444', NULL, NULL), ('Eve', '555 Birch St', '222-333-4444', '2023-05-09 20:30:00', 275), ('Frank', '987 Cedar St', '999-000-1111', '2023-03-12 06:30:00', NULL), ('Frank', '987 Cedar St', '999-000-1111', NULL, 220), ('Frank', '987 Cedar St', '999-000-1111', '2024-02-18 08:40:00', NULL); Теги: #Yandex

Вывести последний актуальный срез и рассчитать сумму всех предыдущих транзакций для каждого клиента (name, address) , не включая текущую. Если в transaction_amount есть NULL, его нужно трактовать как 0 в расчёте суммы. (name, address). ID инкрементальный drop table clients; CREATE TABLE clients ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL, phone TEXT, transaction_date TIMESTAMP DEFAULT now(), transaction_amount DECIMAL ); INSERT INTO clients (name, address, phone, transaction_date, transaction_amount) VALUES ('Alice', '123 Main St', '123-456-7890', NULL, 120), ('Alice', '123 Main St', '123-456-7890', '2024-01-10 14:45:00', NULL), ('Alice', '123 Main St', '123-456-7890', '2024-02-05 16:20:00', 250), ('Alice', '123 Main St', '123-456-7890', NULL, NULL), ('Bob', '456 Elm St', '987-654-3210', '2023-11-22 10:15:00', 180), ('Bob', '456 Elm St', '987-654-3210', '2024-02-25 11:30:00', NULL), ('Bob', '456 Elm St', '987-654-3210', NULL, 220), ('Charlie', '789 Oak St', '555-123-4567', '2023-10-03 09:00:00', 310), ('Charlie', '789 Oak St', '555-123-4567', NULL, 270), ('Charlie', '789 Oak St', '555-123-4567', '2024-05-22 16:50:00', NULL), ('David', '321 Pine St', '777-888-9999', '2022-12-30 22:00:00', 500), ('David', '321 Pine St', '777-888-9999', NULL, 350), ('David', '321 Pine St', '777-888-9999', '2023-09-29 19:30:00', NULL), ('Eve', '555 Birch St', '222-333-4444', '2021-07-04 18:45:00', 150), ('Eve', '555 Birch St', '222-333-4444', NULL, NULL), ('Eve', '555 Birch St', '222-333-4444', '2023-05-09 20:30:00', 275), ('Frank', '987 Cedar St', '999-000-1111', '2023-03-12 06:30:00', NULL), ('Frank', '987 Cedar St', '999-000-1111', NULL, 220), ('Frank', '987 Cedar St', '999-000-1111', '2024-02-18 08:40:00', NULL); Теги: #Yandex

Вывести последний актуальный срез с: Заполнением пропущенных дат Подсчетом суммы всех предыдущих транзакций (без текущей). Обработкой NULL в transaction_amount (замена на 0 при расчете суммы). drop table clients; CREATE TABLE clients ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, address TEXT NOT NULL, phone TEXT, transaction_date TIMESTAMP DEFAULT now(), transaction_amount DECIMAL ); INSERT INTO clients (name, address, phone, transaction_date, transaction_amount) VALUES ('Alice', '123 Main St', '123-456-7890', NULL, 120), ('Alice', '123 Main St', '123-456-7890', '2024-01-10 14:45:00', NULL), ('Alice', '123 Main St', '123-456-7890', '2024-02-05 16:20:00', 250), ('Alice', '123 Main St', '123-456-7890', NULL, NULL), ('Bob', '456 Elm St', '987-654-3210', '2023-11-22 10:15:00', 180), ('Bob', '456 Elm St', '987-654-3210', '2024-02-25 11:30:00', NULL), ('Bob', '456 Elm St', '987-654-3210', NULL, 220), ('Charlie', '789 Oak St', '555-123-4567', '2023-10-03 09:00:00', 310), ('Charlie', '789 Oak St', '555-123-4567', NULL, 270), ('Charlie', '789 Oak St', '555-123-4567', '2024-05-22 16:50:00', NULL), ('David', '321 Pine St', '777-888-9999', '2022-12-30 22:00:00', 500), ('David', '321 Pine St', '777-888-9999', NULL, 350), ('David', '321 Pine St', '777-888-9999', '2023-09-29 19:30:00', NULL), ('Eve', '555 Birch St', '222-333-4444', '2021-07-04 18:45:00', 150), ('Eve', '555 Birch St', '222-333-4444', NULL, NULL), ('Eve', '555 Birch St', '222-333-4444', '2023-05-09 20:30:00', 275), ('Frank', '987 Cedar St', '999-000-1111', '2023-03-12 06:30:00', NULL), ('Frank', '987 Cedar St', '999-000-1111', NULL, 220), ('Frank', '987 Cedar St', '999-000-1111', '2024-02-18 08:40:00', NULL); Теги: #Yandex

# ***не решил.*** Задание 1: Анализ разницы зарплат для сотрудников с минимальной зарплатой Условие: Для сотрудников с минимальной зарплатой (3 минимальных значения ЗП) в разрезе всей компании в мае 2023 года найти разницу зарплат в текущем и предыдущем месяце. Что вывести? Вывести employee_id и department_id. Структура таблицы: CREATE TABLE employee_salaries ( employee_id INT, department_id INT, salary_amt NUMERIC, salary_date DATE ); INSERT INTO employee_salaries (employee_id, department_id, salary_amt, salary_date) VALUES (1, 101, 50000, '2023-04-01'), (1, 101, 52000, '2023-05-01'), (2, 102, 48000, '2023-04-01'), (2, 102, 49000, '2023-05-01'), (3, 103, 45000, '2023-04-01'), (3, 103, 46000, '2023-05-01'), (4, 104, 55000, '2023-04-01'), (4, 104, 56000, '2023-05-01'), (5, 105, 47000, '2023-04-01'), (5, 105, 48000, '2023-05-01'); теги: #лигацифровогоинтернат

Условие: Написать SQL-запрос, который будет находить ошибки в версионности по ключу employee_id, такие как: "Дыры" в истории – пропуски между версиями (from_dt текущей записи больше, чем to_dt предыдущей записи). Пересечение версий – одна и та же запись пересекается с другой (from_dt текущей записи меньше, чем to_dt предыдущей записи). Вывести: employee_id, from_dt, error_type. Структура таблицы: CREATE TABLE salary_versions ( employee_id INT, salary_amt NUMERIC, from_dt DATE, to_dt DATE ); INSERT INTO salary_versions (employee_id, salary_amt, from_dt, to_dt) VALUES (1, 50000, '2019-01-01', '2019-02-01'), (1, 70000, '2019-04-01', '9999-01-01'), (2, 70000, '2019-01-01', '2019-05-01'), (2, 90000, '2019-03-01', '9999-01-01'), (3, 45000, '2019-01-01', '9999-01-01'), (4, 60000, '2020-01-01', '2020-06-01'), (4, 62000, '2020-06-02', '9999-01-01'), (5, 58000, '2021-03-01', '2021-08-01'), (5, 59000, '2021-07-01', '9999-01-01'); теги: #лигацифровогоинтернат

Найти все подъезды и этажи с количеством человек на этаже больше 5. Вывод: entrance, floor, num_persons_by_floor create table building ( entrance int, floor int, flat int, num_persons int ); insert into building (entrance, floor, flat, num_persons) values (1, 1, 11, 4), (1, 2, 21, 3), (1, 2, 22, 1), (1, 3, 31, 1), (1, 4, 41, 1), (1, 5, 51, 2), (1, 6, 61, 1), (1, 7, 71, 2), (1, 8, 81, 5), (1, 9, 91, 5), (1, 10, 101, 1), (1, 11, 111, 5), (1, 12, 121, 5), (8, 4, 64, 4); теги: x5

Найти подъезд (entrance) и этаж (floor) с самым большим суммарным количеством человек. Вывод: entrance, floor, max_num_persons create table building ( entrance int, floor int, flat int, num_persons int ); insert into building (entrance, floor, flat, num_persons) values (1, 1, 11, 4), (1, 2, 21, 3), (1, 2, 22, 1), (1, 3, 31, 1), (1, 4, 41, 1), (1, 5, 51, 2), (1, 6, 61, 1), (1, 7, 71, 2), (1, 8, 81, 5), (1, 9, 91, 5), (1, 10, 101, 1), (1, 11, 111, 5), (1, 12, 121, 5), (8, 4, 64, 4); теги: x5

Вывести для каждого подъезда три самых густонаселённых этажа. Вывод: entrance, floor, top_num_persons теги: x5

Найти преподавателя, который преподаёт у наибольшего количества студентов. Вывод: teacher_id, student_count create table course ( id integer, teacher_id integer, constraint course_pk primary key (id) ); create table enrollment ( student_id integer, course_id integer, constraint enrollment_pk primary key (student_id, course_id) ); insert into course (id, teacher_id) values (1, 100), (2, 100), (3, 101), (4, 102), (5, 101), (6, 100), (7, 103); insert into enrollment (student_id, course_id) values (10, 1), (10, 2), (10, 3), (11, 1), (11, 3), (12, 2), (12, 3), (13, 3), (13, 4), (14, 5), (14, 6), (15, 6), (15, 1), (16, 7), (17, 7); яндекс

Найти студентов, которые записаны хотя бы на 2 курса с одним и тем же преподавателем. Вывод: student_id, teacher_id, num_courses create table course ( id integer, teacher_id integer, constraint course_pk primary key (id) ); create table enrollment ( student_id integer, course_id integer, constraint enrollment_pk primary key (student_id, course_id) ); insert into course (id, teacher_id) values (1, 100), (2, 100), (3, 101), (4, 102), (5, 101), (6, 100), (7, 103); insert into enrollment (student_id, course_id) values (10, 1), (10, 2), (10, 3), (11, 1), (11, 3), (12, 2), (12, 3), (13, 3), (13, 4), (14, 5), (14, 6), (15, 6), (15, 1), (16, 7), (17, 7); яндекс

Найти все пары студентов, которые учатся у одного и того же преподавателя, но не посещают одни и те же курсы. Вывод: student1_id, student2_id, teacher_id create table course ( id integer, teacher_id integer, constraint course_pk primary key (id) ); create table enrollment ( student_id integer, course_id integer, constraint enrollment_pk primary key (student_id, course_id) ); insert into course (id, teacher_id) values (1, 100), (2, 100), (3, 101), (4, 102), (5, 101), (6, 100), (7, 103); insert into enrollment (student_id, course_id) values (10, 1), (10, 2), (10, 3), (11, 1), (11, 3), (12, 2), (12, 3), (13, 3), (13, 4), (14, 5), (14, 6), (15, 6), (15, 1), (16, 7), (17, 7); яндекс