Home Up PDF Prof. Dr. Ingo Claßen
Rated Exercise 2 - DSML

Group G03

Question 1

Turn the following question into SQL

  • All promotions whose EnglishPromotionName starts with ‘V’ and whose MinQty is greater than 20.
  • Rename EnglishPromotionName into Promotion
  • Output Promotion and MinQty
  • Sorted by Promotion

Expected result

┌──────────────────────────┬────────┐
│        Promotion         │ MinQty │
│         varchar          │ int32  │
├──────────────────────────┼────────┤
│ Volume Discount 25 to 40 │     25 │
│ Volume Discount 41 to 60 │     41 │
│ Volume Discount over 60  │     61 │
└──────────────────────────┴────────┘

Question 2

Turn the following question into SQL

  • All organizations and their direct sub organizations
  • Output Organization and SubOrganization
  • Sorted by Organization and SubOrganization

Expected result

┌──────────────────────────┬──────────────────────────┐
│       Organization       │     SubOrganization      │
│         varchar          │         varchar          │
├──────────────────────────┼──────────────────────────┤
│ AdventureWorks Cycle     │ European Operations      │
│ AdventureWorks Cycle     │ North America Operations │
│ AdventureWorks Cycle     │ Pacific Operations       │
│ European Operations      │ France                   │
│ European Operations      │ Germany                  │
│ North America Operations │ Canadian Division        │
│ North America Operations │ USA Operations           │
│ Pacific Operations       │ Australia                │
│ USA Operations           │ Central Division         │
│ USA Operations           │ Northeast Division       │
│ USA Operations           │ Northwest Division       │
│ USA Operations           │ Southeast Division       │
│ USA Operations           │ Southwest Division       │
├──────────────────────────┴──────────────────────────┤
│ 13 rows                                   2 columns │
└─────────────────────────────────────────────────────┘

Question 3

Turn the following question into SQL

  • Number of currencies that don’t have been used in internet sales
  • Output NoOfCurrenciesNotUsed

Expected result

┌───────────────────────┐
│ NoOfCurrenciesNotUsed │
│         int64         │
├───────────────────────┤
│                    99 │
└───────────────────────┘

Question 4

Turn the following question into SQL

  • Cities and number of their corresponding customers. Only cities with more than 200 customers should be shown
  • Output City, NoOfCustomers
  • Sorted by City

Expected result

┌─────────────┬───────────────┐
│    City     │ NoOfCustomers │
│   varchar   │     int64     │
├─────────────┼───────────────┤
│ Beaverton   │           210 │
│ Bellingham  │           210 │
│ Burien      │           212 │
│ Chula Vista │           206 │
│ Concord     │           212 │
│ London      │           420 │
│ Paris       │           386 │
└─────────────┴───────────────┘

Question 5

Turn the following question into SQL

  • Internet Sales amounts per sales territory group for years 2011 an 2012 and difference
  • Output TerritoryGroup, Sales_2011, Sales_2011, Diff
  • Sorted by TerritoryGroup

Expected result

┌────────────────┬───────────────┬───────────────┬───────────────┐
│ TerritoryGroup │  Sales_2011   │  Sales_2012   │     Diff      │
│    varchar     │ decimal(38,2) │ decimal(38,2) │ decimal(38,2) │
├────────────────┼───────────────┼───────────────┼───────────────┤
│ Europe         │    1481936.86 │    1969424.41 │     487487.55 │
│ North America  │    3029857.15 │    1744653.53 │   -1285203.62 │
│ Pacific        │    2563732.37 │    2128407.47 │    -435324.90 │
└────────────────┴───────────────┴───────────────┴───────────────┘

Question 6

Turn the following question into SQL

  • For internet sales amount of USA percentage of all regions
  • Output Country, Region, SalesCountry, SalesRegion, Percentage
  • Sorted by Country, Region

Expected result

┌───────────────┬───────────┬───────────────┬───────────────┬────────────┐
│    Country    │  Region   │ SalesCountry  │  SalesRegion  │ Percentage │
│    varchar    │  varchar  │ decimal(38,2) │ decimal(38,2) │   double   │
├───────────────┼───────────┼───────────────┼───────────────┼────────────┤
│ United States │ Central   │    9389789.94 │       3000.83 │       0.03 │
│ United States │ Northeast │    9389789.94 │       6532.47 │       0.07 │
│ United States │ Northwest │    9389789.94 │    3649866.72 │      38.87 │
│ United States │ Southeast │    9389789.94 │      12238.85 │       0.13 │
│ United States │ Southwest │    9389789.94 │    5718151.07 │       60.9 │
└───────────────┴───────────┴───────────────┴───────────────┴────────────┘

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