MY SQL zadania rekrutacyjne - prośba o pomoc

0

Cześć, to mój pierwszy post więc proszę o wyrozumiałość. Dostałem 3 zadania rekrutacyjne do rozwiązania w SQL, będę wdzięczny za pomoc w ich rozwiązaniu. EDIT: Moje rozwiązania okazały się niewystarczające, określone jako juniorskie więc chciałbym się podszkolić. Więc nie mam tej pracy już jest po ptakach.

Z góry dzięki !!

screenshot-20220614150505.png

  1. Napisz zapytanie przypisujące każdemu klientowi wartość pierwszej wpłaty której dokonał. Nie możemy założyć że wpłaty są insertowane do tabeli payments chronologicznie.

Select
p.payment_client_id as klient,
min(p.payment_date) as 1st TX,
p.payment_value as value,
p.payment_currency as currency
From payments p
Group by p.payment_client_id
order by p.payment_client_id asc;

  1. Napisz zapytanie zwracające klientów wraz z dokonywanymi przez nich wpłatami. Dane powinny dotyczyć tylko wpłat na produkt o nazwie abonament. Dodatkowo do każdej wpłaty dodaj wartość poprzedniej wpłaty dokonanej przez tego samego klienta na ten sam produkt. Przykład co powinno zwracać zapytanie, kolory są wyłącznie pomocnicze:
    NazwaKlienta DataWplaty WartoscWplaty WarPoprzWplaty
    Adam 2020-01-01 50 NULL
    Adam 2020-02-04 65 50
    Adam 2020-03-02 70 65
    Anna 2020-02-20 100 NULL
    Anna 2020-04-20 150 100
    Karol 2020-06-01 50 NULL

Select c.client_name as NazwaKlienta,
p.payment_date as DataWplaty,
p.payments_value as WartoscWplaty
Case
when count(p.payment_id) = 1
then null
when count(p.payment_id) =2
then p.payment.value = # tutaj brak pomysłu jak to rozwiązać

From payments p
join clients c
On c.client_ID = p.payment_client_id

Join products pr
On pr.product_id = p.payment_product_id

Where pr.product_name = ‘abonament’

Group by NazwaKlienta
Order by DataWplaty asc;

  1. Napisz zapytanie zwracające sumę wpłat dokonanych przez ostatnie pełne 12 miesięcy na produkty o nazwie abonament lub kredyt. Wyniki dla tych dwóch produktów powinny być w dwóch osobnych kolumnach. Przykład jak może wyglądać rozwiązanie:
    Miesiąc WynikiAbonament WynikiKredyt
    Listopad 1500 5600
    Grudzień 1700 6000
    Styczeń 2000 6100
    Luty 1900 6000

Select year(p.payment_date)||monthofyear(p.payment_date) as rok_miesiąc, if(pr.product_name like ‘Abonament’, sum(p.payment_value),0) as WynikiAbonament, if(pr.product_name like ‘kredyt’, sum(p.payment_value),0) as WynikiKredyt

From payments p
Left join products pr
On pr.product_id = p.payment_product_id

Where p.payments_date between ‘2022-05-31’ and ‘2021-06-01’

Group by rok_miesiąc
Order by rok_miesiąc asc;

0

@SQL_93:

A oddasz pensję jak cię przyjmą?

0
  1. jakie stanowisko?
  2. jaka wersja MySQL?
  3. Czy mozna używać CTE?
0

Pomogę, ale jedynie za 20% każdej wypłaty, tzw helping tax.

PS. to tylko sarkazm

1

Dobrze, że się nie dostałeś, bo nauczyli by Cię robić diagramy, według których, klient ma dokładnie 1 wpłatę wymaganą. Jak utworzyć klienta bez wpłaty? ;-)

Napisz zapytanie przypisujące każdemu klientowi wartość pierwszej wpłaty której dokonał. Nie możemy założyć że wpłaty są insertowane do tabeli payments chronologicznie.

Krok1: Z płatności wyciągasz datę pierwszej wpłaty


select  
 payment_client_id,
 min(payment_date) earliest_payment_date
from payments p  
group by payment_client_id;

Krok2: Łączysz płatności tym wciągniętym zbiorem "pierwsza płatność".

Krok3: Łączysz z klientami (opcjonalnie, bo co robić z ziomkami, którzy nie dokonali żadnej płatności?)

with first_payment_date as (
select  
 payment_client_id,
 min(payment_date) earliest_payment_date
from payments p  
group by payment_client_id
)
select 
  c.client_id,
  p.payment_value 
from clients c 
join payments p on p.payment_client_id = c.client_id
join first_payment_date fp on fp.payment_client_id = p.payment_client_id and p.payment_date = fp.earliest_payment_date  
;

Problem#1 - Model na to pozwala, więc trzeba doprecyzować co z tym fantem zrobić. Co jeśli klient ma 2 płatności z tą samą datą?
a) zsumować "pierwsze" płatności
b) zwrócić dwa rekordy per klient
c) wybrać jakąkolwiek z danego dnia
d) inne regułka określająca co to znaczy "pierwsza płatność" jeśli płatności mają tę samą datę

Problem#2 - co jeśli klient nie ma płatności, zwracać NULL, 0, nie zwracać klienta ? To determinuje, czy robić CLIENTS LEFT JOIN PAYMENTS, czy wystarczą dane z PAYMENTS.

Zapytanie, które zrobiłeś:

Select
p.payment_client_id as klient,
min(p.payment_date) as 1st TX,
p.payment_value as value,
p.payment_currency as currency
From payments p
Group by p.payment_client_id
order by p.payment_client_id asc;

Grupujesz tylko po payment_client_id, więc pewnie jakaś MySQLowa magia wkracza do akcji i coś tam produkuje, czy poprawnie, nie wiem. Na innym silniku zapytanie prawdopodobnie się wywali.

Można też użyć funkcji analitycznych i czytać tylko tabelkę PAYMENTS, rekordy dzielimy na grupy względem klienta, w ramach grupy zdajemy kolejność po payment_date i wybieramy pierwszy rekord z grupy.

select 
  payment_client_id, 
  payment_value 
from
(
  select 
    payment_client_id,
    payment_value, 
    row_number() over (partition by payment_client_id order by payment_date) rn
  from payments 
) where rn=1;
4
  1. BI i MySQL nie należy używać w jednym zdaniu. :)
  2. No sorry, ale na Analityk BI to trzeba to znać. Ja właśnie robię rekrutację na Juniora i uważam, że też powinien to znać. Tu naprawdę nie ma nic trudnego.
  3. Każde z tych zapytań można rozwiązać na kilka (a może nawet kilkanaście) sposobów.
  4. http://sqlfiddle.com/#!17/03ffe/1
0

@Marcin.Miga: serdecznie dziekuję za szczegółowe opracowanie zadań, doceniam, że poświęciłeś na to swój czas wolny! Po zgłębieniu tematu, zgadzam się z Wami, że to faktycznie raczej prosta sprawa.

@yarel Tobie również dziękuje za pomoc

1 użytkowników online, w tym zalogowanych: 0, gości: 1