Лайфкодинг SQL

Все темы Python Developer

⌨️ Задачи

Задача №1

Пусть имеем таблицу с 3 столбцами: id, name и age, которая содержит 100 млн строк.
Допустим, неизвестно, является ли id первичным ключом таблицы.
Хотим проверить, так ли это. Каким простым запросом можно сделать такую валидацию?
Допустим, нужно добавить в эту таблицу столбец с количеством раз, которое каждое имя (поле name)
встречается в таблице. Как это сделать? Допустим, нужно определить возраст,
который чаще всего встречается в таблице.

Как это сделать?

Решение:

• Этот запрос проверяет отсутствие NULL и повторов в текущих данных id. Наличие ограничения PRIMARY KEY проверяют отдельно в метаданных схемы.

SELECT COUNT(*) AS total_rows, COUNT(id) AS non_null_ids,
       COUNT(DISTINCT id) AS distinct_ids
FROM your_table_name;

Если все три значения равны, в текущих данных id нет NULL и повторов. Это ещё не означает, что в схеме объявлен PRIMARY KEY.

• Чтобы добавить столбец с количеством раз, которое каждое имя встречается в таблице, можно использовать следующий запрос:

ALTER TABLE your_table_name
ADD COLUMN name_count INT;

UPDATE your_table_name
SET name_count = (SELECT COUNT(*) FROM your_table_name t2 WHERE t2.name = your_table_name.name);

Этот запрос добавляет новый столбец name_count в таблицу и затем обновляет его значения, подсчитывая количество строк с каждым именем.

• Чтобы определить возраст, который чаще всего встречается в таблице, можно использовать следующий запрос:

SELECT age
FROM your_table_name
GROUP BY age
ORDER BY COUNT(*) DESC
LIMIT 1;

Этот запрос группирует строки по возрасту, считает количество строк для каждого возраста, затем сортирует результаты по убыванию количества строк и выбирает самый часто встречающийся возраст.

Задача №2

Для каждого клиента вычислить средний интервал между заказами в днях, используя следующие таблицы:
Таблица order содержит следующие столбцы:
- order_id: уникальный идентификатор заказа.
- date_time: дата и время заказа.
- amount: сумма заказа.
- customer_id: идентификатор клиента, связанный с заказом.
Таблица customer содержит следующие столбцы:
- customer_id: уникальный идентификатор клиента.
- age: возраст клиента.
Для каждого клиента необходимо вычислить средний интервал между его заказами в днях.

Для вычисления среднего интервала между заказами для каждого клиента, мы можем сделать следующее:

1. Сначала для каждого клиента упорядочим заказы по date_time и order_id и найдём предыдущий заказ с помощью LAG.

2. Вычислим длительность между соседними заказами в секундах и переведём в дни.

3. Наконец, мы сгруппируем результаты по customer_id и вычислим среднее значение интервала для каждого клиента.

Вот как это можно сделать в SQL:

WITH intervals AS (
    SELECT customer_id,
           TIMESTAMPDIFF(SECOND,
               LAG(date_time) OVER (
                   PARTITION BY customer_id ORDER BY date_time, order_id
               ), date_time) / 86400.0 AS interval_days
    FROM `order`
)
SELECT c.customer_id, AVG(i.interval_days) AS avg_interval_days
FROM customer c
LEFT JOIN intervals i ON i.customer_id = c.customer_id
GROUP BY c.customer_id;

Запрос для MySQL 8 вычисляет интервалы между соседними заказами и усредняет их по клиенту. Для клиентов с нулём или одним заказом средний интервал равен NULL. При одинаковом времени заказов учитывается нулевой интервал.

Задача №3

Предложить SQL запрос для оценки качества данных в динамике по заполненности.
В таблице table есть отчётные даты report_dt на конец месяца, номера договоров id не Null.
Названия полей по которым анализируем пропуски: feature1, feature2.

Вид таблицы: table(report_dt, id, feature1, feature2)

Пример строки:
'2024-01-31', 457294, 1, Null
'2024-02-28', 784934, Null, 3

Решение:

SELECT report_dt,
       COUNT(id) AS total_records,
       COUNT(feature1) AS filled_feature1,
       COUNT(feature2) AS filled_feature2,
       1.0 * COUNT(feature1) / COUNT(*) AS feature1_completion_rate,
       1.0 * COUNT(feature2) / COUNT(*) AS feature2_completion_rate
FROM "table"
GROUP BY report_dt
ORDER BY report_dt;

Задача №4

-- Вот условие задания:
CREATE TABLE shop (
    id      SERIAL          PRIMARY KEY,
    name    VARCHAR(255)    NOT NULL,
    city    VARCHAR(255)    NOT NULL
);

CREATE TABLE cheque (
    uid         UUID            NOT NULL    DEFAULT gen_random_uuid(),
    created_at  TIMESTAMP       NOT NULL    DEFAULT now(),
    "sum"       DECIMAL(10, 2)  NOT NULL,
    shop_id     INT             NOT NULL,
    customer_id BIGINT          NOT NULL,
    PRIMARY KEY (uid),
    FOREIGN KEY (shop_id) REFERENCES shop (id)
);

-- Вот само задание, которое нужно решить:
-- Вывести средний чек для покупателя за последнюю неделю,
-- когда у него была покупка:
-- покупатель - средний чек - дата последней покупки.

Решение для PostgreSQL. Под последней неделей понимаем календарную неделю с последней покупкой каждого клиента.

WITH last_dates AS (
    SELECT customer_id, MAX(created_at) AS last_purchase_date
    FROM cheque
    GROUP BY customer_id
)
SELECT c.customer_id,
       AVG(c."sum") AS average_check,
       d.last_purchase_date
FROM cheque c
JOIN last_dates d ON c.customer_id = d.customer_id
WHERE c.created_at >= date_trunc('week', d.last_purchase_date)
  AND c.created_at < date_trunc('week', d.last_purchase_date) + INTERVAL '1 week'
GROUP BY c.customer_id, d.last_purchase_date;

Задача №5

Есть две таблицы А=10 строк В=50 строк какое максимальное и минимальное количество строк получим при A left join B

При выполнении операции left join (или left outer join) в SQL, результат будет содержать все строки из таблицы A, даже если в таблице B нет соответствующих строк.

Таким образом, минимальное количество строк в результирующей таблице будет равно количеству строк в таблице A, то есть 10 строк.

Максимум составляет 10 × 50 = 500 строк, если каждая строка A соответствует каждой строке B по условию соединения.