תרגיל זה הוא ברמת קושי קשה ומתרגל אלמנטים של פונקציות חלון ו- תת שאילתה ב- שפת 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 |
אנא חשבו את ממוצע וסטיית התקן של הימים שעברו בין הרכישות של כל לקוח.
שימו לב:
- יש לסנן את ההזמנות שסכומם קטן מ 0 (אלו החזרים ולא הזמנות).
- יש לסנן לקוחות שרכשו רק פעם אחת כי הם אינם לקוחות חוזרים.
select
customer_id,
avg(days_elapsed) as avg_days_elapsed,
stddev(days_elapsed) as std_days_elapsed
from
(
select
order_date-lag(order_date) over(partition by customer_id order by order_date asc) as days_elapsed,
count(order_id) over (partition by customer_id) as customer_orders
,customer_id
from
e_commerce.orders
where
sum_order > 0
) as orders_with_last_order_date
where
customer_orders>1
group by
1
הסבר:
בשאילתה הפנימית חישבנו את הימים שעברו בעזרת פונקציית החלון Lag ואת כמות הרכישות של כל לקוח. כמו כן, הסרנו את ההזמנות שקטנות שסכום ההזמנה שלהם קטן מאפס.
בשאילתה החיצונית סיננו את הלקוחות שהזמינו פעם אחת וחישבנו את הממוצע וסטית התקן לכל לקוח לי הימים שעברו בין הקניות שלו.
