Group G05

Question 1

Turn the following question into SQL

All products whose EnglishProductName starts with ‘W’
Rename EnglishProductName into Product
Output Product
Sorted by Product

Expected result

┌────────────────────────────┐
│          Product           │
│          varchar           │
├────────────────────────────┤
│ Water Bottle - 30 oz.      │
│ Women's Mountain Shorts, L │
│ Women's Mountain Shorts, M │
│ Women's Mountain Shorts, S │
│ Women's Tights, L          │
│ Women's Tights, M          │
│ Women's Tights, S          │
└────────────────────────────┘

Question 2

Turn the following question into SQL

All department groups and their direct sub groups
Output Group and SubGroup
Sorted by Group and SubGroup

Expected result

┌───────────┬──────────────────────────────────────┐
│   Group   │               SubGroup               │
│  varchar  │               varchar                │
├───────────┼──────────────────────────────────────┤
│ Corporate │ Executive General and Administration │
│ Corporate │ Inventory Management                 │
│ Corporate │ Manufacturing                        │
│ Corporate │ Quality Assurance                    │
│ Corporate │ Research and Development             │
│ Corporate │ Sales and Marketing                  │
└───────────┴──────────────────────────────────────┘

Question 3

Turn the following question into SQL

All cities within France that don’t have any customers
Output City
Sorted by City

Expected result

┌────────────────┐
│      City      │
│    varchar     │
├────────────────┤
│ Aujan Mournede │
└────────────────┘

Question 4

Turn the following question into SQL

Scenarios and number of their corresponding finance facts. Only scenarios with less than 30000 fincance facts should be shown
Output Scenario, NoOfFinanceFacts
Sorted by Scenario

Expected result

┌──────────┬──────────────────┐
│ Scenario │ NoOfFinanceFacts │
│ varchar  │      int64       │
├──────────┼──────────────────┤
│ Budget   │             4846 │
│ Forecast │                0 │
└──────────┴──────────────────┘

Question 5

Turn the following question into SQL

Internet Sales amounts per product category for years 2011 an 2012 and difference
Output ProductCategory, Sales_2011, Sales_2011, Diff
Sorted by ProductCategory

Expected result

┌─────────────────┬───────────────┬───────────────┬───────────────┐
│ ProductCategory │  Sales_2011   │  Sales_2012   │     Diff      │
│     varchar     │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │
├─────────────────┼───────────────┼───────────────┼───────────────┤
│ Accessories     │          0.00 │       2147.08 │       2147.08 │
│ Bikes           │    7075526.38 │    5839695.54 │   -1235830.84 │
│ Clothing        │          0.00 │        642.79 │        642.79 │
└─────────────────┴───────────────┴───────────────┴───────────────┘

Question 6

Turn the following question into SQL

For internet sales amount of year 2013 percentage of all month
Output Year, Month, SalesYear, SalesMonth, Percentage
Sorted by Year, MonthNumber

Expected result

┌───────┬───────────┬───────────────┬───────────────┬────────────┐
│ Year  │   Month   │   SalesYear   │  SalesMonth   │ Percentage │
│ int32 │  varchar  │ decimal(38,2) │ decimal(38,2) │   double   │
├───────┼───────────┼───────────────┼───────────────┼────────────┤
│  2013 │ January   │   16351550.34 │     857689.91 │       5.25 │
│  2013 │ February  │   16351550.34 │     771348.74 │       4.72 │
│  2013 │ March     │   16351550.34 │    1049907.39 │       6.42 │
│  2013 │ April     │   16351550.34 │    1046022.77 │        6.4 │
│  2013 │ May       │   16351550.34 │    1284592.93 │       7.86 │
│  2013 │ June      │   16351550.34 │    1643177.78 │      10.05 │
│  2013 │ July      │   16351550.34 │    1371675.81 │       8.39 │
│  2013 │ August    │   16351550.34 │    1551065.56 │       9.49 │
│  2013 │ September │   16351550.34 │    1447495.69 │       8.85 │
│  2013 │ October   │   16351550.34 │    1673293.41 │      10.23 │
│  2013 │ November  │   16351550.34 │    1780920.06 │      10.89 │
│  2013 │ December  │   16351550.34 │    1874360.29 │      11.46 │
├───────┴───────────┴───────────────┴───────────────┴────────────┤
│ 12 rows                                              5 columns │
└────────────────────────────────────────────────────────────────┘

Question 7

Turn the following question into SQL

Internet sales amount of product category Bikes in 2012 on month level also year over year in 2011
Output Category, Year, Month, YearPrevious, MonthPrevious, Sales, SalesPrevious, SalesDiff
Sorted by Category, Year, Month

Expected result

┌──────────┬───────┬───────────┬──────────────┬───────────────┬───────────────┬───────────────┬───────────────┐
│ Category │ Year  │   Month   │ YearPrevious │ MonthPrevious │     Sales     │ SalesPrevious │   SalesDiff   │
│ varchar  │ int32 │  varchar  │    int32     │    varchar    │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │
├──────────┼───────┼───────────┼──────────────┼───────────────┼───────────────┼───────────────┼───────────────┤
│ Bikes    │  2012 │ January   │         2011 │ January       │     495364.01 │     469823.94 │      25540.07 │
│ Bikes    │  2012 │ February  │         2011 │ February      │     506994.08 │     466334.93 │      40659.15 │
│ Bikes    │  2012 │ March     │         2011 │ March         │     373482.95 │     485198.69 │    -111715.74 │
│ Bikes    │  2012 │ April     │         2011 │ April         │     400335.59 │     502073.88 │    -101738.29 │
│ Bikes    │  2012 │ May       │         2011 │ May           │     358877.87 │     561681.51 │    -202803.64 │
│ Bikes    │  2012 │ June      │         2011 │ June          │     555160.18 │     737839.87 │    -182679.69 │
│ Bikes    │  2012 │ July      │         2011 │ July          │     444558.28 │     596746.60 │    -152188.32 │
│ Bikes    │  2012 │ August    │         2011 │ August        │     523917.45 │     614557.98 │     -90640.53 │
│ Bikes    │  2012 │ September │         2011 │ September     │     486177.50 │     603083.53 │    -116906.03 │
│ Bikes    │  2012 │ October   │         2011 │ October       │     535159.56 │     708208.05 │    -173048.49 │
│ Bikes    │  2012 │ November  │         2011 │ November      │     537955.67 │     660545.86 │    -122590.19 │
│ Bikes    │  2012 │ December  │         2011 │ December      │     621712.40 │     669431.54 │     -47719.14 │
├──────────┴───────┴───────────┴──────────────┴───────────────┴───────────────┴───────────────┴───────────────┤
│ 12 rows                                                                                           8 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Question 8

Turn the following question into SQL

Provide customer lifetime information
Output
- CustomerKey
- TotalOrders
  - Number of distinct purchase orders a customer has placed over its entire relationship with the business
- FirstPurchaseDate
  - Date of a customers very first purchase transaction in the system
- LastPurchaseDate
  - Date of a customers most recent purchase transaction
- UniquePurchaseDays
  - Number of distinct calendar days on which a customer made at least one purchase
- DaysSinceLastPurchase
  - Number of days from the customers last purchase to 2014-01-28
  - 2014-01-28 is the max of all purchase dates in the system
- CustomerTenureDays
  - Number of days between a customers first and last purchase
- CustomerTenureYears
  - Converts CustomerTenureDays into years
  - Based on precise astronomical year length (365.25 days to account for leap years)
- AvgDaysBetweenOrders
  - Typical number of days between consecutive purchases
- OrdersPerYear
  - Annualizes the order frequency
- FrequencyQuintile
  - Ranks customers into five equal groups (quintiles) based on total order count
  - With 5 being the most frequent purchasers and 1 being the least frequent
Sorted by CustomerKey

Use COUNT(DISTINCT column_name) for distinct orders, unique day, etc

Expected result

┌─────────────┬─────────────┬─────────────────────┬─────────────────────┬────────────────────┬───────────────────────┬────────────────────┬─────────────────────┬──────────────────────┬───────────────┬───────────────────┐
│ CustomerKey │ TotalOrders │  FirstPurchaseDate  │  LastPurchaseDate   │ UniquePurchaseDays │ DaysSinceLastPurchase │ CustomerTenureDays │ CustomerTenureYears │ AvgDaysBetweenOrders │ OrdersPerYear │ FrequencyQuintile │
│    int32    │    int64    │      timestamp      │      timestamp      │       int64        │         int64         │       int64        │       double        │        double        │    double     │       int64       │
├─────────────┼─────────────┼─────────────────────┼─────────────────────┼────────────────────┼───────────────────────┼────────────────────┼─────────────────────┼──────────────────────┼───────────────┼───────────────────┤
│       11000 │           3 │ 2011-01-19 00:00:00 │ 2013-05-03 00:00:00 │                  3 │                   270 │                835 │                2.29 │                417.5 │          1.31 │                 1 │
│       11001 │           3 │ 2011-01-15 00:00:00 │ 2013-12-10 00:00:00 │                  3 │                    49 │               1060 │                 2.9 │                530.0 │          1.03 │                 1 │
│       11002 │           3 │ 2011-01-07 00:00:00 │ 2013-02-23 00:00:00 │                  3 │                   339 │                778 │                2.13 │                389.0 │          1.41 │                 1 │
│       11003 │           3 │ 2010-12-29 00:00:00 │ 2013-05-10 00:00:00 │                  3 │                   263 │                863 │                2.36 │                431.5 │          1.27 │                 1 │
│       11004 │           3 │ 2011-01-23 00:00:00 │ 2013-05-01 00:00:00 │                  3 │                   272 │                829 │                2.27 │                414.5 │          1.32 │                 1 │
│       11005 │           3 │ 2010-12-30 00:00:00 │ 2013-05-02 00:00:00 │                  3 │                   271 │                854 │                2.34 │                427.0 │          1.28 │                 1 │
│       11006 │           3 │ 2011-01-24 00:00:00 │ 2013-05-14 00:00:00 │                  3 │                   259 │                841 │                 2.3 │                420.5 │           1.3 │                 1 │
│       11007 │           3 │ 2011-01-09 00:00:00 │ 2013-03-19 00:00:00 │                  3 │                   315 │                800 │                2.19 │                400.0 │          1.37 │                 1 │
│       11008 │           3 │ 2011-01-25 00:00:00 │ 2013-03-02 00:00:00 │                  3 │                   332 │                767 │                 2.1 │                383.5 │          1.43 │                 1 │
│       11009 │           3 │ 2011-01-27 00:00:00 │ 2013-05-09 00:00:00 │                  3 │                   264 │                833 │                2.28 │                416.5 │          1.32 │                 1 │
│         ·   │           · │          ·          │          ·          │                  · │                    ·  │                 ·  │                  ·  │                  ·   │            ·  │                 · │
│         ·   │           · │          ·          │          ·          │                  · │                    ·  │                 ·  │                  ·  │                  ·   │            ·  │                 · │
│         ·   │           · │          ·          │          ·          │                  · │                    ·  │                 ·  │                  ·  │                  ·   │            ·  │                 · │
│       20990 │           2 │ 2011-12-21 00:00:00 │ 2013-04-11 00:00:00 │                  2 │                   292 │                477 │                1.31 │                477.0 │          1.53 │                 2 │
│       20991 │           2 │ 2011-12-05 00:00:00 │ 2013-04-27 00:00:00 │                  2 │                   276 │                509 │                1.39 │                509.0 │          1.44 │                 1 │
│       20992 │           2 │ 2011-11-30 00:00:00 │ 2013-04-05 00:00:00 │                  2 │                   298 │                492 │                1.35 │                492.0 │          1.48 │                 1 │
│       20993 │           2 │ 2011-11-30 00:00:00 │ 2013-04-24 00:00:00 │                  2 │                   279 │                511 │                 1.4 │                511.0 │          1.43 │                 2 │
│       20994 │           2 │ 2011-11-30 00:00:00 │ 2013-04-02 00:00:00 │                  2 │                   301 │                489 │                1.34 │                489.0 │          1.49 │                 2 │
│       20995 │           2 │ 2011-11-29 00:00:00 │ 2013-04-23 00:00:00 │                  2 │                   280 │                511 │                 1.4 │                511.0 │          1.43 │                 1 │
│       20996 │           2 │ 2011-12-05 00:00:00 │ 2013-04-12 00:00:00 │                  2 │                   291 │                494 │                1.35 │                494.0 │          1.48 │                 1 │
│       20997 │           2 │ 2012-01-18 00:00:00 │ 2013-04-06 00:00:00 │                  2 │                   297 │                444 │                1.22 │                444.0 │          1.64 │                 1 │
│       20998 │           2 │ 2012-01-26 00:00:00 │ 2013-04-24 00:00:00 │                  2 │                   279 │                454 │                1.24 │                454.0 │          1.61 │                 2 │
│       20999 │           2 │ 2012-01-03 00:00:00 │ 2013-04-15 00:00:00 │                  2 │                   288 │                468 │                1.28 │                468.0 │          1.56 │                 1 │
├─────────────┴─────────────┴─────────────────────┴─────────────────────┴────────────────────┴───────────────────────┴────────────────────┴─────────────────────┴──────────────────────┴───────────────┴───────────────────┤
│ ? rows (>9999 rows, 20 shown)                                                                                                                                                                                 11 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Question 9

Turn the following question into SQL

Provide customer cohort information
Output
- CohortPeriod
  - The year-month when customers made their first purchase
  - As YYYY-MM
- CohortSize
  - Total number of unique customers who made their first purchase in that cohort period
- MonthsFromCohortStart
  - How many months have passed since the cohort’s first purchase (0 = first month, 1 = second month, etc.)
- ActiveCustomers
  - Number of customers from the cohort who made purchases in that specific month
- RetentionPct
  - Percentage of the original cohort still making purchases (ActiveCustomers ÷ CohortSize × 100)
- TotalOrders
  - Total number of orders placed by the cohort in that month
- TotalRevenue
  - Total sales amount generated by the cohort in that month
- AvgOrdersPerCustomer
  - Average orders per active customer in that month (TotalOrders ÷ ActiveCustomers)
- AvgRevenuePerCustomer
  - Average revenue per active customer in that month (TotalRevenue ÷ ActiveCustomers)
- Sorted by CohortPeriod

Only regard internet sales

Output only cohort that start in 2013-01

Expected result

┌──────────────┬────────────┬───────────────────────┬─────────────────┬──────────────┬─────────────┬───────────────┬──────────────────────┬───────────────────────┐
│ CohortPeriod │ CohortSize │ MonthsFromCohortStart │ ActiveCustomers │ RetentionPct │ TotalOrders │ TotalRevenue  │ AvgOrdersPerCustomer │ AvgRevenuePerCustomer │
│   varchar    │   int64    │         int32         │      int64      │    float     │   int128    │ decimal(38,2) │        double        │        double         │
├──────────────┼────────────┼───────────────────────┼─────────────────┼──────────────┼─────────────┼───────────────┼──────────────────────┼───────────────────────┤
│ 2013-01      │        325 │                     0 │             325 │        100.0 │         327 │     316218.85 │                 1.01 │                972.98 │
│ 2013-01      │        325 │                     1 │              17 │         5.23 │          22 │        979.68 │                 1.29 │                 57.63 │
│ 2013-01      │        325 │                     2 │              15 │         4.62 │          23 │       1171.13 │                 1.53 │                 78.08 │
│ 2013-01      │        325 │                     3 │              19 │         5.85 │          30 │       6257.97 │                 1.58 │                329.37 │
│ 2013-01      │        325 │                     4 │              16 │         4.92 │          30 │       3734.47 │                 1.88 │                 233.4 │
│ 2013-01      │        325 │                     5 │              22 │         6.77 │          28 │       3593.14 │                 1.27 │                163.32 │
│ 2013-01      │        325 │                     6 │              17 │         5.23 │          33 │       1829.97 │                 1.94 │                107.65 │
│ 2013-01      │        325 │                     7 │              19 │         5.85 │          34 │       6489.78 │                 1.79 │                341.57 │
│ 2013-01      │        325 │                     8 │              14 │         4.31 │          19 │        910.00 │                 1.36 │                  65.0 │
│ 2013-01      │        325 │                     9 │              19 │         5.85 │          29 │       2071.41 │                 1.53 │                109.02 │
│ 2013-01      │        325 │                    10 │              20 │         6.15 │          32 │       6469.24 │                  1.6 │                323.46 │
│ 2013-01      │        325 │                    11 │              15 │         4.62 │          26 │       1148.08 │                 1.73 │                 76.54 │
│ 2013-01      │        325 │                    12 │              17 │         5.23 │          23 │       1114.63 │                 1.35 │                 65.57 │
├──────────────┴────────────┴───────────────────────┴─────────────────┴──────────────┴─────────────┴───────────────┴──────────────────────┴───────────────────────┤
│ 13 rows                                                                                                                                               9 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘