SQL. Задачи с собесов (draft).

Некоторые задачи из подборки не с собесов, а из тестовых заданий, которые раньше давали на дом. Была такая эпоха, до пришествия ChatGPT и прочих. Но сейчас такие задачи на онлайн кодинге наваливают. Все скрипты приведены для PostgreSQL.

Заметка будет дополняться со временем.

1)Имеется таблица курсов валют следующей структуры:

create table scott.rates(
curr_id int, — ид валюты
date_rate DATE, — дата курса
rate numeric)

Курс валюты устанавливается не на каждую календарную дату и действует до следующей смены курса

Уникальный ключ: curr_id + date_rate.

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

Требуемый результат:

Для валюты 1 на 03.01.2010 получить курс 32

Для валюты 2 на 10.01.2010 получить курс 41

Решение через оконные функции самое универсальное, хотя в PostgreSQL можно решить через distinct on - см заметку (а в Oracle через keep (dense_rank ...)).

Решение:

with prep as (
select * 
,row_number() over (partition by curr_id order by date_rate desc) rn
from scott.rates
where true
and curr_id = 1
and date_rate <= '2010-01-03'
)
select * from prep where rn = 1

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

2)Посчитайте по таблице FactSales скользящее среднее по продажам (поле SalesAmount) за окно в 3 дня (время в поле OrderDate) в разрезе StoreId, ProductId.

FactSales
OrderDate
StoreId
ProductId
SalesAmount

Решение:

Select f.*
, avg(FactSales) over(partition by StoreId, ProductId 
order by OrderDate rows between 2 preceding and current row) running_avg
From FactSales f

3)Дана таблица валют (справочник), необходимо написать запрос, который возвращает отсортированный список валют в алфавитном порядке по столбцу ISO_CODE, причем первыми должны идти основные валюты, с которыми работает банк: RUR, USD, EUR.

create table scott.currency_dict (iso_code text, iso_name text);

Решение:

select
iso_code,
iso_name
from scott.currency_dict
order by 
  case iso_code when  'RUR'then 1 when 'USD' then 2 when 'EUR' then 3 end
  , iso_code

4)Необходимо получить в результате запроса только актуальные данные по каждой товарной позиции и дате начала действия ее цены
из этих данных построить периоды действия где дата окончания действия текущей цены является датой начала действия следующей -1 день

create table scott.scd2 (  
article     int,     --id товарной позиции
price       numeric,   --цена
date_from   date,    --дата начала действия цены
date_change date    --техническое поле даты изменения версии строки SCD2
)

Честно признаться, ни на собесе, ни сейчас не понял, что нужно сделать. Мутное ТЗ. Нужно уточнять, пока такой вариант решения.

with base as (
select
    article,
    price,
    date_from,
    row_number() over (partition by article, date_from order by date_change desc) as rn
from scott.scd2

)
select
    article,
    price,
    date_from,
    lead(date_from, 1, '4000-01-01'::date) over (partition by article order by date_from) as date_to
from base
where rn = 1

5) Вариация на тему задачи 1

Напишите sql запрос, который будет переводить сумму транзакций из rub в usd (ccy_code = 840) с учетом того, что в таблице rates данные только за рабочие дни. Транзакции, совершенные в выходные, пересчитываются по курсу последнего рабочего дня перед праздником/выходным. Результат: Клиент, дата, сумма операций в usd.

Решение:

select client_id, t.report_date, txn_amount/r.ccy_rate amount_usd
from dbo.transactions t
left join lateral
(select * from dbo.rates r 
where r.ccy_code  = '840' 
and r.report_date <= t.report_date 
order by r.report_date desc limit 1) r 
  on true

Это, кстати, частый обоснованный пример использования lateral join - получение TOP N значений в внешнем запросе. В том же MS SQL Server 2005 такое ещё 10+ лет назад приходилось делать, но используя кляузу outer apply вместо lateral join. В оракеле с 12 версии тоже так можно делать.

6)

В таблице oper_data содержится информация по транзакциям клиентов в офисах физической сети. txn_type принимает значения debit, credit

Напишите sql запрос, который для каждого клиента выводит сумму debit, credit операций и последний посещенный офис по месяцам. Результат представьте в виде:

Решение:

select client_id, report_date
,sum (case when txn_type = 'debit' then txn_amount else 0 end) over (partition by client_id, date_part('month', report_date)) debit_amount
,sum (case when txn_type = 'credit' then txn_amount else 0 end) over (partition by client_id, date_part('month', report_date)) credit_amount
,last_value (office_number) over (partition by client_id, date_part('month', report_date) 
  order by report_date  rows between unbounded preceding and unbounded following) last_office
from dbo.oper_data

Тут вместо староверного подсчёта суммы через case (я - старовер) можно использовать кляузу filter. Ну и помнить про такие оконки, как first value/last value. Я их в проде ни разу не использовал. ¯\_(ツ)_/¯
Можно обойтись без last_value, если вынести расчёт последнего посещенного офиса в разрезе клиента и месяца в CTE, а потом зажойнить с расчитанными дебетовыми и кредитовыми оборотами.

Это такая не редкая задача на собесах - показать, что ты владеешь магией написать sum (case when ... Иногда даже достаточно это проговорить, что ты знаком с этой магией)).