Data Modeling Interview Questions Explained

import duckdb

db = duckdb.connect()
db.execute("INSTALL excel; LOAD excel")
db.execute("CREATE TABLE raw AS SELECT * FROM read_xlsx('Online Retail.xlsx', all_varchar = true)")   # UCI Online Retail
db.execute("""CREATE TABLE lines AS SELECT InvoiceNo AS invoice, StockCode AS product, TRIM(Description) AS description,
    CAST(Quantity AS INTEGER) AS qty, ROUND(CAST(UnitPrice AS DOUBLE), 2) AS price,
    CAST(CAST(CustomerID AS DOUBLE) AS INTEGER) AS customer, Country AS country,
    TIMESTAMP '1899-12-30' + to_seconds(CAST(ROUND(CAST(InvoiceDate AS DOUBLE) * 86400) AS BIGINT)) AS ts
    FROM raw""")


def ask(title, sql):
    print(f"-- {title}")
    for row in db.execute(sql).fetchall():
        print("  ", *row)


ask("D1 grain: one fact row per invoice line", "SELECT COUNT(*) AS lines, COUNT(DISTINCT invoice) AS invoices FROM lines")

ask("D2 does price belong to the product? prices recorded for product 85123A", """
    SELECT COUNT(DISTINCT price), MIN(price), MAX(price), ANY_VALUE(description) FROM lines WHERE product = '85123A'""")

ask("D3 product codes with more than one description, and some of the extra ones", """
    SELECT COUNT(*) FROM (SELECT product FROM lines WHERE description IS NOT NULL
                          GROUP BY product HAVING COUNT(DISTINCT description) > 1)""")
ask("   descriptions seen on stock code 23071", """
    SELECT description, COUNT(*) AS lines, SUM(qty) AS units FROM lines WHERE product = '23071' GROUP BY description ORDER BY lines DESC""")
db.execute("""CREATE TABLE dim_product AS
    SELECT ROW_NUMBER() OVER (ORDER BY product) AS product_key, product AS stock_code, description
    FROM (SELECT product, description, ROW_NUMBER() OVER (PARTITION BY product ORDER BY COUNT(*) DESC, description) AS n
          FROM lines WHERE description IS NOT NULL AND price > 0 GROUP BY product, description)
    WHERE n = 1
    UNION ALL SELECT 0, NULL, 'Unknown product'""")
ask("   dim_product keeps the most used priced description per code", "SELECT COUNT(*) - 1, ANY_VALUE(description) FILTER (WHERE stock_code = '23071') FROM dim_product")

ask("D4 customers recorded under more than one country", """
    SELECT COUNT(*) FROM (SELECT customer FROM lines WHERE customer IS NOT NULL GROUP BY customer HAVING COUNT(DISTINCT country) > 1)""")
db.execute("""CREATE TABLE dim_customer AS
    WITH visits AS (SELECT customer, country, MIN(ts) AS first_seen FROM lines WHERE customer IS NOT NULL GROUP BY customer, invoice, country),
         changes AS (SELECT *, LAG(country) OVER (PARTITION BY customer ORDER BY first_seen) AS previous FROM visits),
         versions AS (SELECT customer, country, first_seen AS valid_from FROM changes WHERE previous IS NULL OR previous <> country)
    SELECT ROW_NUMBER() OVER (ORDER BY customer, valid_from) AS customer_key, customer, country, valid_from,
           LEAD(valid_from) OVER (PARTITION BY customer ORDER BY valid_from) AS valid_to
    FROM versions
    UNION ALL SELECT 0, NULL, 'Unknown', TIMESTAMP '1900-01-01', NULL""")
ask("   dim_customer as type 2: customers, rows, rows for customer 12417", """
    SELECT COUNT(DISTINCT customer), COUNT(*), (SELECT COUNT(*) FROM dim_customer WHERE customer = 12417) FROM dim_customer""")
ask("   customer 12417's versions", "SELECT customer_key, country, valid_from, valid_to FROM dim_customer WHERE customer = 12417 ORDER BY valid_from")

db.execute("""CREATE TABLE fact_sales AS
    SELECT l.invoice, CAST(l.ts AS DATE) AS date_key, COALESCE(c.customer_key, 0) AS customer_key,
           COALESCE(p.product_key, 0) AS product_key, l.qty, l.price, l.qty * l.price AS revenue
    FROM lines l
    LEFT JOIN dim_product p ON p.stock_code = l.product
    LEFT JOIN dim_customer c ON c.customer = l.customer AND l.ts >= c.valid_from AND (c.valid_to IS NULL OR l.ts < c.valid_to)""")
ask("D5 unknown members: lines without a customer or product, and revenue kept vs lost", """
    SELECT (SELECT COUNT(*) FROM fact_sales WHERE customer_key = 0) AS unknown_customer_lines,
           (SELECT COUNT(*) FROM fact_sales WHERE product_key = 0) AS unknown_product_lines,
           ROUND((SELECT SUM(revenue) FROM fact_sales f JOIN dim_customer c USING (customer_key))) AS revenue_with_unknown_member,
           ROUND((SELECT SUM(qty * price) FROM lines l JOIN (SELECT DISTINCT customer FROM dim_customer) c USING (customer))) AS revenue_joining_on_null_ids""")

db.execute("""CREATE TABLE dim_date AS
    SELECT CAST(d AS DATE) AS date_key, DAYNAME(d) AS weekday, MONTH(d) AS month
    FROM range(DATE '2010-12-01', DATE '2011-12-10', INTERVAL 1 DAY) t(d)""")
ask("D6 a date dimension shows the days with no sales at all", """
    SELECT d.weekday, COUNT(*) AS days, COUNT(*) FILTER (WHERE f.date_key IS NULL) AS days_without_sales
    FROM dim_date d LEFT JOIN (SELECT DISTINCT date_key FROM fact_sales) f USING (date_key)
    GROUP BY d.weekday ORDER BY days_without_sales DESC, d.weekday LIMIT 3""")

ask("D7 distinct customers are not additive", """
    SELECT (SELECT SUM(n) FROM (SELECT COUNT(DISTINCT customer_key) AS n FROM fact_sales
                                WHERE customer_key <> 0 GROUP BY MONTH(date_key), YEAR(date_key))) AS sum_of_monthly_counts,
           (SELECT COUNT(DISTINCT customer) FROM dim_customer WHERE customer IS NOT NULL) AS customers_in_the_year""")

-- D1 grain: one fact row per invoice line
   541909 25900
-- D2 does price belong to the product? prices recorded for product 85123A
   8 0.0 5.91 WHITE HANGING HEART T-LIGHT HOLDER
-- D3 product codes with more than one description, and some of the extra ones
   647
--    descriptions seen on stock code 23071
   MARIE ANTOINETTE TRINKET BOX GOLD 27 242
   damages 1 -65
   MARIE ANTOIENETT TRINKET BOX GOLD 1 1
--    dim_product keeps the most used priced description per code
   3937 MARIE ANTOINETTE TRINKET BOX GOLD
-- D4 customers recorded under more than one country
   8
--    dim_customer as type 2: customers, rows, rows for customer 12417
   4372 4389 3
--    customer 12417's versions
   60 Belgium 2010-12-17 11:51:00 2011-04-28 13:09:00
   61 Spain 2011-04-28 13:09:00 2011-06-05 15:46:00
   62 Belgium 2011-06-05 15:46:00 None
-- D5 unknown members: lines without a customer or product, and revenue kept vs lost
   135080 138 9747748.0 8300066.0
-- D6 a date dimension shows the days with no sales at all
   Saturday 53 53
   Monday 53 6
   Friday 54 4
-- D7 distinct customers are not additive
   13680 4372