תרגיל זה הוא ברמת קושי קשה ומתרגל אלמנטים של subqueries, join, windows function בשפת SQL.
תרגיל מסוג זה יכול להופיע בראיונות עבודה לתפקיד של דאטה אנליסט.
התרגיל נכתב ע"י יובל מרנין.
לעמוד התרגילים המלא לחצו כאן.
לפניך חלק מטבלת orders מתוך דאטה-בייס בשם e_commerce
טבלת Orders
| order_time_stamp | order_date | order_date_hour | avg_unit_price | sum_order | item_orders | country | customer_id | order_id |
|---|---|---|---|---|---|---|---|---|
| 2010-12-01 08:35:00 | 2010-12-01 | 12/1/2010 8:35 | 5.9499998093 | 17.8499994278 | 1 | United Kingdom | 13,047 | 536369 |
| 2010-12-01 08:45:00 | 2010-12-01 | 12/1/2010 8:45 | 2.764500013 | 855.8600058556 | 20 | France | 12,583 | 536370 |
| 2010-12-01 09:00:00 | 2010-12-01 | 12/1/2010 9:00 | 2.5499999523 | 203.9999961853 | 1 | United Kingdom | 13,748 | 536371 |
| 2010-12-01 09:01:00 | 2010-12-01 | 12/1/2010 9:01 | 1.8500000238 | 22.2000002861 | 2 | United Kingdom | 17,850 | 536372 |
| 2010-12-01 09:02:00 | 2010-12-01 | 12/1/2010 9:02 | 3.3193749636 | 259.8599972725 | 16 | United Kingdom | 17,850 | 536373 |
| 2010-12-01 09:09:00 | 2010-12-01 | 12/1/2010 9:09 | 10.9499998093 | 350.3999938965 | 1 | United Kingdom | 15,100 | 536374 |
| 2010-12-01 09:32:00 | 2010-12-01 | 12/1/2010 9:32 | 3.3193749636 | 259.8599972725 | 16 | United Kingdom | 17,850 | 536375 |
| 2010-12-01 09:32:00 | 2010-12-01 | 12/1/2010 9:32 | 3 | 328.7999992371 | 2 | United Kingdom | 15,291 | 536376 |
| 2010-12-01 09:34:00 | 2010-12-01 | 12/1/2010 9:34 | 1.8500000238 | 22.2000002861 | 2 | United Kingdom | 17,850 | 536377 |
| 2010-12-01 09:37:00 | 2010-12-01 | 12/1/2010 9:37 | 1.7552631701 | 444.9800012112 | 19 | United Kingdom | 14,688 | 536378 |
| 2010-12-01 09:41:00 | 2010-12-01 | 12/1/2010 9:41 | 1.4500000477 | 34.8000011444 | 1 | United Kingdom | 17,809 | 536380 |
| 2010-12-01 09:41:00 | 2010-12-01 | 12/1/2010 9:41 | 2.5199999877 | 449.9799970388 | 35 | United Kingdom | 15,311 | 536381 |
| 2010-12-01 09:45:00 | 2010-12-01 | 12/1/2010 9:45 | 5.9708333512 | 430.6000010967 | 12 | United Kingdom | 16,098 | 536382 |
| 2010-12-01 09:53:00 | 2010-12-01 | 12/1/2010 9:53 | 4.7807691831 | 489.5999960899 | 13 | United Kingdom | 18,074 | 536384 |
| 2010-12-01 09:56:00 | 2010-12-01 | 12/1/2010 9:56 | 5.5714286906 | 130.8500015736 | 7 | United Kingdom | 17,420 | 536385 |
| 2010-12-01 09:57:00 | 2010-12-01 | 12/1/2010 9:57 | 2.7499999205 | 508.1999883652 | 3 | United Kingdom | 16,029 | 536386 |
| 2010-12-01 09:58:00 | 2010-12-01 | 12/1/2010 9:58 | 2.6519999504 | 3,193.9199638367 | 5 | United Kingdom | 16,029 | 536387 |
| 2010-12-01 09:59:00 | 2010-12-01 | 12/1/2010 9:59 | 3.3764285211 | 226.1399983168 | 14 | United Kingdom | 16,250 | 536388 |
| 2010-12-01 10:03:00 | 2010-12-01 | 12/1/2010 10:03 | 5.2785713673 | 358.2499952316 | 14 | Australia | 12,431 | 536389 |
| 2010-12-01 10:19:00 | 2010-12-01 | 12/1/2010 10:19 | 2.4529166635 | 1,825.7399904728 | 24 | United Kingdom | 17,511 | 536390 |
| 2010-12-01 10:29:00 | 2010-12-01 | 12/1/2010 10:29 | 18.398999989 | 318.1399998665 | 10 | United Kingdom | 13,705 | 536392 |
| 2010-12-01 10:37:00 | 2010-12-01 | 12/1/2010 10:37 | 9.9499998093 | 79.5999984741 | 1 | United Kingdom | 13,747 | 536393 |
| 2010-12-01 10:39:00 | 2010-12-01 | 12/1/2010 10:39 | 2.1881817931 | 1,024.6800034046 | 11 | United Kingdom | 13,408 | 536394 |
| 2010-12-01 10:47:00 | 2010-12-01 | 12/1/2010 10:47 | 2.5707142821 | 507.8799939156 | 14 | United Kingdom | 13,767 | 536395 |
| 2010-12-01 10:51:00 | 2010-12-01 | 12/1/2010 10:51 | 5.2116666238 | 376.359995842 | 18 | United Kingdom | 17,850 | 536396 |
| 2010-12-01 10:51:00 | 2010-12-01 | 12/1/2010 10:51 | 4.6500000954 | 279.000005722 | 2 | United Kingdom | 17,924 | 536397 |
אנא כיתובו את השאילתות הבאות:
- מצאו את הלקוחות שהזמינו ביום עם מספר ההזמנות הכי גבוה בטבלת ההזמנות.
- מצאו את הלקוחות שהזמינו ביום עם מספר ההזמנות במקום השני הכי גבוה בטבלת ההזמנות.
select
distinct
customer_id
from
e_commerce.orders as orders
— start sub query orders_by_date_ranked_filtered —
inner join
(
select
*
— start sub query orders_by_date_ranked —
from
(
select
order_date,
orders,
rank () over (order by orders desc) as rk
— start sub query orders_by_date —
from
(
select
order_date,
count(order_id) as orders
from
e_commerce.orders
group by
1
) as orders_by_date
— end sub query orders_by_date —
) as orders_by_date_ranked
— end sub query orders_by_date_ranked —
where
orders_by_date_ranked.rk=1
) as orders_by_date_ranked_filtered
— end sub query orders_by_date_ranked_filtered —
on
orders_by_date_ranked_filtered.order_date=orders.order_date
הסבר:
בשביל למצא את היום הכי משמעותי אנחנו מיצרים טבלה אגרגטיבית שבה לכל יום יש את כמות ההזמנות שלו. לאחר מכן, אנחנו מדרגים את הטבלה לפי כמות הזמנות.
לאחר הדרוג, אנחנו מפלטרים את התאריך שמדורג הכי גבוה בכמות ההזמנות (rk=1) ומחברים אותו ב inner join לטבלת ההזמנות הכללית.
בטבלת ההזמנות הכללית אנחנו בוחרים רק את הלקוחות, וכדי להימנע ממקרים כפולים (לקוחות שהזמינו פעמיים באותו היום) אנחנו משתמשים ב distinct.
עבור הסעיף השני בשאלה – יש לבחור rk=2 בשביל היום עם הכי הרבה הזמנות במקום השני.
