Group G04

Question 1

Turn the following question into SQL

All customers whose BirthDate is between December 2, 1977 and December 4, 1977
Output LastName, Firstname, BirthDate
Sorted by BirthDate

Expected result

┌──────────┬───────────┬────────────┐
│ LastName │ FirstName │ BirthDate  │
│ varchar  │  varchar  │    date    │
├──────────┼───────────┼────────────┤
│ Rana     │ Kaitlin   │ 1977-12-03 │
│ Martinez │ Jarrod    │ 1977-12-04 │
│ Long     │ Ethan     │ 1977-12-04 │
└──────────┴───────────┴────────────┘

Question 2

Turn the following question into SQL

All employees whose LastName starts with ‘Ha’ and their direct subordinates
Output Boss and Employee
Sorted by Boss and Employee

Expected result

┌────────────────┬───────────────────┐
│      Boss      │     Employee      │
│    varchar     │      varchar      │
├────────────────┼───────────────────┤
│ David Hamilton │ Baris Cetinok     │
│ David Hamilton │ Gary Yukish       │
│ David Hamilton │ Michael Rothkugel │
│ David Hamilton │ Nicole Holliday   │
│ David Hamilton │ Paul Komosinski   │
│ David Hamilton │ Rob Caron         │
│ James Hamilton │ Gary Altman       │
│ Jeff Hay       │ Alex Nayberg      │
│ Jeff Hay       │ Andrew Cencini    │
│ Jeff Hay       │ Chris Preston     │
│ Jeff Hay       │ Kirk Koenigsbauer │
│ Jeff Hay       │ Laura Steele      │
├────────────────┴───────────────────┤
│ 12 rows                  2 columns │
└────────────────────────────────────┘

Question 3

Turn the following question into SQL

All sub categories that don’t have any products whose color is black
Output SubCategory
Sorted by SubCategory

Expected result

┌───────────────────┐
│    SubCategory    │
│      varchar      │
├───────────────────┤
│ Bib-Shorts        │
│ Bike Racks        │
│ Bike Stands       │
│ Bottles and Cages │
│ Bottom Brackets   │
│ Brakes            │
│ Caps              │
│ Chains            │
│ Cleaners          │
│ Derailleurs       │
│   ·               │
│   ·               │
│   ·               │
│ Locks             │
│ Panniers          │
│ Pedals            │
│ Pumps             │
│ Saddles           │
│ Socks             │
│ Tires and Tubes   │
│ Touring Bikes     │
│ Touring Frames    │
│ Vests             │
├───────────────────┤
│      27 rows      │
│    (20 shown)     │
└───────────────────┘

Question 4

Turn the following question into SQL

Promotion types and number of their corresponding internet sales facts. Only promotion types with less than 3000 internet sales facts should be shown
Output PromotionType, NoOfSalesFacts
Sorted by PromotionType

Expected result

┌──────────────────────┬────────────────┐
│    PromotionType     │ NoOfSalesFacts │
│       varchar        │     int64      │
├──────────────────────┼────────────────┤
│ Discontinued Product │              0 │
│ Excess Inventory     │              0 │
│ New Product          │             33 │
│ Seasonal Discount    │              0 │
│ Volume Discount      │           2118 │
└──────────────────────┴────────────────┘

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 product category bikes percentage of all sub categories
Output Category, SubCategory, SalesProduct, SalesSubProduct, Percentage
Sorted by Category, SubCategory

Expected result

┌──────────┬────────────────┬───────────────┬─────────────────┬────────────┐
│ Category │  SubCategory   │ SalesProduct  │ SalesSubProduct │ Percentage │
│ varchar  │    varchar     │ decimal(38,2) │  decimal(38,2)  │   double   │
├──────────┼────────────────┼───────────────┼─────────────────┼────────────┤
│ Bikes    │ Mountain Bikes │   28318145.32 │      9952760.77 │      35.15 │
│ Bikes    │ Road Bikes     │   28318145.32 │     14520583.50 │      51.28 │
│ Bikes    │ Touring Bikes  │   28318145.32 │      3844801.05 │      13.58 │
└──────────┴────────────────┴───────────────┴─────────────────┴────────────┘

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 │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘