📍Горячие пирожки тестовые. Налетаем, разбираем, изучаем!

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

✅ Задача 1
Какая неделя стала лучшей по прибыли и указать саму сумму прибыли. Описания полей смотрите на картинке.
В таблице кроится небольшая хитрость. Есть колонка price_b2b_gross и колонка price_b2c_gross, а колонка количества купленных товаров в заказе только одна и не понятно к чему она относится – к b2b (юр.лицам) или к b2c (физ.лицам)
Считается, что вы или спросите или сами сделаете допущение, что наверно к b2c.

Загружаем файл в Юпитер (скачать ноутбук можно здесь , а файл с excel данными - вот тут берем), получаем датаффрейм и воспользовавшись библиотекой pandasql (о ней писала вот тут) пишете sql код. Не забываем, что эта библиотека реализует язык запросов СУБД SQLite. Поэтому есть конечно ряд ограничений, нельзя использовать операторы как в PostreSQL Например, DATE_PART('week', date_create). Нужно найти его аналогию и это strftime('%W',date_create), которая вытащит вам номер недели.

Сначала находим группировкой сумму прибыли за каждую неделю, не забываем указать в фильтре where status = 0 Так как в описании полей прописано, что Статус заказа (0 - Выдан, остальные - Отменен) А потом сортируем по сумме от большего к меньшему (order by 2 desc, 2, так как вторая колонка ) и берем ограничение в 1 строку limit 1. Ну и округлим до 2х знаков после запятой round() для красоты.

Select strftime('%W',date_create) as 'лучшая неделя',

round(sum(price_b2c_gross*quantity-cost_price*quantity),2) as 'сумма прибыли'

From df

where status = 0

group by 1

order by 2 desc

limit 1

Есть нюанс, что если вот прям две недели совпали один в один, то вы потеряете инфу про вторую такую прибыльную неделю. Так как указан limit в одну строку. Вряд ли такое возможно, но есть вероятность. ⚡️
Поэтому покажем и 2ой способ скрипта. И чем больше вы продемонстрируете как вы можете сделать задачку и придумать способы, тем лучше. 👌

🌟 Сначала так же находим максимальную сумму за неделю. Потом находим, как и в первом варианте все недели и их суммы и в условии where прописываем, чтобы сумма из таблички с недельными суммами равнялась той максимальной сумме.

Select weekofyear as 'лучшая неделя', round(amount,2) as 'сумма прибыли'

from

(

Select strftime('%W',date_create) AS weekofyear,

       sum(price_b2c_gross*quantity-cost_price*quantity) as amount

from df

where status = 0

group by 1

) t1

-- фильтруем то, где сумма за неделю (t1.amount) будет равна максимальной сумме

where t1.amount =  (Select max(amount) -- находим максимальную сумму за неделю

                    From

                    (Select strftime('%W',date_create) AS weekofyear,

                            sum(price_b2c_gross*quantity-cost_price*quantity) as amount

                    From df

                    where status = 0

                    group by 1)

                    t2)

Мы можем уже обратиться тут к переменной amount, так как мы ее в подзапросе создали. И помним, что если подзапрос идем как табличка после FROM, то нужно указать ее название. Просто t1, допустим.

✅ Задача 2
Рассчитать маржу в рублях и % по всей компании за август. Вывести поля: Сайт, Приложение Строки: Маржа руб., Маржа %
Предоставить запрос в SQL и значения.

Во первых, маржа - это разница между себестоимостью товара и ценой, по которой продают товар. Почему тогда в первом задании назвали то же самое суммой прибыли? А вот и не важно, это тоже для того, чтобы вы подумали и поняли, что по сути это то же самое и формула будет такой же price_b2c_gross*quantity-cost_price*quantity

Продолжение в комментариях. 👉