תרגיל זה הוא ברמת קושי בינונית ומתרגל אלמנטים פונקציות חלון בשפת SQL.
התרגיל נכתב ע"י יובל מרנין.
לעמוד התרגילים המלא לחצו כאן.
לפניך חלק מטבלת customers מתוך דאטה-בייס בשם e_commerce
| avg_order | sum_orders | orders | last_order | first_order | country | customer_id |
|---|---|---|---|---|---|---|
| 214.3831564674 | 4,073.2799728811 | 19 | 2011-12-05 | 2011-02-25 | United Kingdom | 16,592 |
| 390.2108135815 | 1,447,682.1183872335 | 3,710 | 2011-12-09 | 2010-12-01 | Unspecified | [NULL] |
| 112.7099992633 | 450.8399970531 | 4 | 2011-11-29 | 2011-05-04 | United Kingdom | 14,173 |
| 282.9699971676 | 2,263.7599773407 | 8 | 2011-11-06 | 2011-01-17 | United Kingdom | 13,527 |
| 187.3499979824 | 374.6999959648 | 2 | 2011-10-07 | 2011-09-04 | United Kingdom | 14,067 |
| 744.7739968061 | 3,723.8699840307 | 5 | 2011-09-05 | 2011-02-03 | Spain | 12,502 |
| 1,013.0099925995 | 1,013.0099925995 | 1 | 2011-11-10 | 2011-11-10 | United Kingdom | 17,197 |
| 153.1199979782 | 153.1199979782 | 1 | 2011-10-12 | 2011-10-12 | France | 12,602 |
| 595.4976872068 | 7,741.4699336886 | 13 | 2011-03-17 | 2010-12-01 | United Kingdom | 13,093 |
| 435.968750298 | 3,487.7500023842 | 8 | 2011-10-12 | 2010-12-03 | Switzerland | 13,520 |
| 84.7033323348 | 254.1099970043 | 3 | 2011-11-10 | 2011-06-16 | United Kingdom | 14,765 |
| 382.7999964952 | 382.7999964952 | 1 | 2011-11-15 | 2011-11-15 | United Kingdom | 18,181 |
| 472.7816674709 | 2,836.6900048256 | 6 | 2011-10-12 | 2011-04-01 | United Kingdom | 14,852 |
| 324.6285712378 | 2,272.3999986649 | 7 | 2011-11-10 | 2010-12-02 | United Kingdom | 14,466 |
| 206.9799995422 | 206.9799995422 | 1 | 2011-01-24 | 2011-01-24 | United Kingdom | 15,820 |
| 382.6699956506 | 765.3399913013 | 2 | 2011-07-31 | 2011-06-21 | United Kingdom | 17,695 |
| 271.0454526706 | 2,981.4999793768 | 11 | 2011-12-01 | 2011-01-13 | United Kingdom | 15,687 |
| 90.7249996662 | 362.8999986649 | 4 | 2011-11-10 | 2011-09-07 | United Kingdom | 17,573 |
| 155.1700005531 | 155.1700005531 | 1 | 2011-01-13 | 2011-01-13 | United Kingdom | 15,447 |
| 591.8291273726 | 13,612.06992957 | 23 | 2011-12-08 | 2010-12-09 | Germany | 12,621 |
מצאו את הלקוחות שסכום רכישתם הכולל (sum_orders) הוא מעל הממוצע.
בשביל פתרון אנא גללו למטה.
select
customer_id
from
(
select
*,
avg(sum_orders) over () as avg_sum_order
from
e_commerce.customers
) as tab
where sum_orders>avg_sum_order
