🖥 Задача

Компания определяет своих суперпользователей как тех, кто совершил не менее двух транзакций. Из следующей таблицы (см картинку) напишите запрос, чтобы вернуть для каждого пользователя дату, когда он стал суперпользователем, сначала тех, кто стал раньше суперпользователем. Пользователи, которые не являются суперпользователями, также должны присутствовать в таблице.

Решение

WITH users (user_id, action, action_date)
AS (VALUES
(1, 'start', CAST('2-12-20' AS date)),
(1, 'cancel', CAST('2-13-20' AS date)),
(2, 'start', CAST('2-11-20' AS date)),
(2, 'publish', CAST('2-14-20' AS date)),
(3, 'start', CAST('2-15-20' AS date)),
(3, 'cancel', CAST('2-15-20' AS date)),
(4, 'start', CAST('2-18-20' AS date)),
(1, 'publish', CAST('2-19-20' AS date))),
-- create a date rank column, partitioned by user ID, using the ROW_NUMBER() window function
t1 AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY action_date DESC) AS date_rank
FROM users ),
-- filter on date rank column to pull latest and next latest actions from this table
latest AS (
SELECT *
FROM t1
WHERE date_rank = 1 ),
next_latest AS (
SELECT *
FROM t1
WHERE date_rank = 2 )
-- left join these two tables, subtracting latest from second latest to get time elapsed
SELECT
l1.user_id,
l1.action_date - l2.action_date AS days_elapsed
FROM latest l1
LEFT JOIN next_latest l2
ON l1.user_id = l2.user_id
ORDER BY 1

@sqlhub

🖥 Задачи SQL с решениями. Десять практических упражнений SQL

Эти вопросы и примеры решений помогут Вам развить свои навыки в SQL.

➡️ Смотреть

@sqlhub

🖥 SQL Задачи.

--На входе у нас есть таблица
DECLARE @pays TABLE (
client_id int --Идентификатор клиента
,date datetime --Дата и время платежа
,pay_sum decimal(19,2) --Сумма платежа
)

-- Заполняем таблицу тестовыми данными

INSERT INTO @pays VALUES (1, '2022-01-01T16:11:11', 10)
INSERT INTO @pays VALUES (2, '2022-01-01T16:12:12', 20)
INSERT INTO @pays VALUES (3, '2022-01-01T16:13:13', 30)
INSERT INTO @pays VALUES (5, '2022-02-01T16:14:14', 50)
INSERT INTO @pays VALUES (3, '2022-02-01T16:15:15', 35)


Напишите запрос, который выбирал бы все данные из таблицы с добавлением 2 полей:
- сумма платежа нарастающим итогом
- количество записей выше текущей для записей с тем же идентификатором клиента
При этом записи упорядочиваются сначала по дате, потом по идентификатору клиента.


SELECT select_1.client_id, select_1.date, select_1.pay_sum, sm, cnt
FROM
(SELECT a.client_id, a.date, a.pay_sum, SUM(b.pay_sum) sm
FROM @pays a
LEFT OUTER JOIN @pays b
ON a.date >= b.date
GROUP BY a.client_id, a.date, a.pay_sum) select_1
INNER JOIN
(SELECT a.client_id, a.date, a.pay_sum, count(c.client_id) cnt
FROM @pays a
LEFT OUTER JOIN @pays c
on a.client_id = c.client_id
AND a.date > c.date
GROUP BY a.client_id, a.date, a.pay_sum,c.client_id) select_2
ON select_1.client_id = select_2.client_id
AND select_1.date = select_2.date
AND select_1.pay_sum = select_2.pay_sum
ORDER BY select_1.date, select_1.client_id

@sqlhub

🖥 SQL Задачи.

Даны 3 таблицы: таблица category с полями id и name, таблица sub_category с полями id и name и таблица page с полями id, name и sub_category_id. Достаньте одним запросом все страницы вместе с их подкатегориями и категориями.

SELECT * FROM page LEFT JOIN category ON page.catogory_id = category.id
LEFT JOIN sub_category ON category.id = sub_category.category_id

@sqlhub

🖥 SQL: быстрое погружение 2022

Автор: Шилдс Уолтер

Книга идеальна для всех, кто ищет новые перспективы карьерного роста; для разработчиков, которые хотят расширить свои навыки и знания в программировании.

➡️ Книга

@sqlhub

🖥 Как откатить часть транзакции внутри функции или процедуры?
На SQL вы можете сделать так:

BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint; --rollback previous command
INSERT INTO table1 VALUES (3);
COMMIT;

Внутри функции или процедуры код выше завершится с ошибкой, например в is_sql.sql. Но вы можете откатить часть SQL команд в транзакции через подтранзакции:

DO $TEST$
BEGIN
-- here you can write DDL commands, for example, adding or deleting a table or its section
-- and/or
-- here you can write DML commands that modify data in tables and, thus, check the operation of triggers

-- rollback all test queries
raise exception using errcode = 'query_canceled';

EXCEPTION WHEN query_canceled THEN
--don't do anything
END
$TEST$;

@sqlhub

🖥 Как модифицировать данные в нескольких таблицах и вернуть id затронутых записей в одном запросе?

WITH
updated AS (
UPDATE table1
SET x = 5, y = 6 WHERE z > 7
RETURNING id
),
inserted AS (
INSERT INTO table2 (x, y, z) VALUES (5, 7, 10)
RETURNING id
)
SELECT 'table1_updated' AS action, id
FROM updated
UNION ALL
SELECT 'table2_inserted' AS action, id
FROM inserted;

@sqlhub

🖥 Расскажите о видах индексов

Простые индексы. Создаются только для одного столбца таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name(column_name);

Составные индексы. Создаются для двух или большего количества столбцов таблицы.

Синтаксис:

CREATE INDEX index_name
ON table_name (column1, column2)

Уникальные индексы. Используются для поддержания целостности данных таблицы. Они не дают вставлять в таблицу несколько значений.

Синтаксис:

CREATE UNIQUE INDEX index
ON table_name(column_name)

@sqlhub

🖥 Вопросы с собеседования по анализу данных SQL в 2023 году.

Вы готовитесь к собеседованию по SQL для анализ данных? Тогда вы пришли в нужное место!

Это руководство поможет вам усовершенствовать свои навыки работы с SQL, вернуть уверенность в себе и быть готовым к работе!

Здесь вы найдёте подборку реальных вопросов для собеседований, задаваемых в таких компаниях, как Google, Oracle, Amazon, Microsoft и т.д. К каждому вопросу прилагается идеально написанный ответ, что экономит ваше время на подготовку к собеседованию.

➡️ Читать дальше

@sqlhub

🖥 Как обезопасить приложение от тяжёлых миграций, приводящих к блокированию запросов?

#PostgreSQL

Этот код не даёт 100% гарантии, а только уменьшает количество заблокированных запросов. После того, как блокировка взята, другие запросы, могут встать в очередь, ожидая отпускания блокировки.

Вначале каждой миграции, которая выполняется внутри транзакции, нужно изменить настройки конфигурации lock_timeout и statement_timeout и idle_in_transaction_session_timeout командой SET LOCAL. Действие SET LOCAL продолжается только до конца текущей транзакции, независимо от того, фиксируется она или нет. При выполнении такой команды вне блока транзакции выдаётся предупреждение и больше ничего не происходит.

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

BEGIN;
DO $$
DECLARE
exception_message text;
exception_context text;
BEGIN
/*
Задаёт максимальную длительность ожидания любым оператором получения блокировки таблицы, индекса, строки или другого объекта базы данных.
Если ожидание не закончилось за указанное время, оператор прерывается.
Это ограничение действует на каждую попытку получения блокировки по отдельности и применяется как к явным запросам блокировки
(например, LOCK TABLE или SELECT FOR UPDATE без NOWAIT), так и к неявным.
Если это значение задаётся без единиц измерения, оно считается заданным в миллисекундах.
*/
SET LOCAL lock_timeout TO '3s';
-- Максимальное время выполнения любого SQL запроса в этой транзакции. Если будет превышено, то транзакция откатится.
SET LOCAL statement_timeout TO '30min';
-- Максимальное время простаивания транзакции, PostgreSQL >= 10. Если будет превышено, то транзакция откатится.
SET LOCAL idle_in_transaction_session_timeout TO '10s';
EXCEPTION WHEN undefined_object THEN
GET STACKED DIAGNOSTICS
exception_message = MESSAGE_TEXT,
exception_context = PG_EXCEPTION_CONTEXT;
RAISE NOTICE '%', exception_context;
RAISE NOTICE '%', exception_message;
END $$;


/*
Здесь SQL команды для наката (или отката) внутри транзакции
*/
COMMIT;

/*
Здесь SQL команды для наката (или отката), которые не могут работать внутри транзакции
*/

Если транзакция откатится, то есть 2 варианта: запустить повторно во время меньших нагрузок или оптимизировать код миграции, чтобы свести к минимуму блокировки.

@sqlhub

🖥 Как упростить себе жизнь при разработке интерфейса взаимодействия приложения и БД?

же давно существуют ORM-фреймворки (англ. Object-Relational Mapping). ORM – это та самая прослойка между приложением и БД, с помощью которой можно, управляя объектами в приложении, синхронизировать их с объектами в БД, а также избавиться от необходимости вручную реализовывать DAL — то есть, не прописывать как должен выглядеть SQL-запрос на CRUD-операцию, не раскладывать переменные объекта по местам в запросе, не задавать приведение к типам/размерность, и т. д.

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

Для большинства языков существует масса ORM-фреймворков, например, SQLAlchemy для Python, Entity Framework для .NET, Hibernate для Java. Опираясь на свои предпочтения, я буду разбирать типовые фишки ORM с помощью .NET Core/ EF Core/MS SQL.

➡️ Читать дальше

@sqlhub