Query for larger DVDRental

Revenue By Month

SET final = 1;

SELECT
    toStartOfMonth(payment_date) AS month,
    count() AS payments,
    round(sum(amount), 2) AS revenue,
    round(avg(amount), 2) AS avg_payment
FROM helloworld.public_payment
WHERE _peerdb_is_deleted = 0
GROUP BY month
ORDER BY month;

Top Customer by Lifetime Value

SELECT
    c.customer_id,
    concat(c.first_name, ' ', c.last_name) AS customer_name,
    c.email,
    count(p.payment_id) AS payments,
    round(sum(p.amount), 2) AS lifetime_value,
    max(p.payment_date) AS last_payment_at
FROM helloworld.public_customer c
JOIN helloworld.public_payment p
    ON p.customer_id = c.customer_id
WHERE
    c._peerdb_is_deleted = 0
    AND p._peerdb_is_deleted = 0
GROUP BY
    c.customer_id,
    customer_name,
    c.email
ORDER BY lifetime_value DESC
LIMIT 25;

Most Rented Film

SELECT
    f.film_id,
    f.title,
    c.name AS category,
    count(r.rental_id) AS rental_count
FROM helloworld.public_rental r
JOIN helloworld.public_inventory i
    ON i.inventory_id = r.inventory_id
JOIN helloworld.public_film f
    ON f.film_id = i.film_id
JOIN helloworld.public_film_category fc
    ON fc.film_id = f.film_id
JOIN helloworld.public_category c
    ON c.category_id = fc.category_id
WHERE
    r._peerdb_is_deleted = 0
    AND i._peerdb_is_deleted = 0
    AND f._peerdb_is_deleted = 0
    AND fc._peerdb_is_deleted = 0
    AND c._peerdb_is_deleted = 0
GROUP BY
    f.film_id,
    f.title,
    c.name
ORDER BY rental_count DESC
LIMIT 20;

Rental Duration Distribution

SELECT
    quantile(0.5)(dateDiff('hour', rental_date, return_date)) AS p50_hours,
    quantile(0.9)(dateDiff('hour', rental_date, return_date)) AS p90_hours,
    quantile(0.99)(dateDiff('hour', rental_date, return_date)) AS p99_hours,
    avg(dateDiff('hour', rental_date, return_date)) AS avg_hours
FROM helloworld.public_rental
WHERE
    _peerdb_is_deleted = 0
    AND return_date IS NOT NULL;

Customer with overdue or long rentals

SELECT
    c.customer_id,
    concat(c.first_name, ' ', c.last_name) AS customer_name,
    f.title,
    r.rental_date,
    r.return_date,
    dateDiff('day', r.rental_date, coalesce(r.return_date, now())) AS rental_days
FROM helloworld.public_rental r
JOIN helloworld.public_customer c
    ON c.customer_id = r.customer_id
JOIN helloworld.public_inventory i
    ON i.inventory_id = r.inventory_id
JOIN helloworld.public_film f
    ON f.film_id = i.film_id
WHERE
    r._peerdb_is_deleted = 0
    AND c._peerdb_is_deleted = 0
    AND i._peerdb_is_deleted = 0
    AND f._peerdb_is_deleted = 0
    AND dateDiff('day', r.rental_date, coalesce(r.return_date, now())) > 7
ORDER BY rental_days DESC
LIMIT 50;

Revenue by Store and Month

SELECT
    s.store_id,
    toStartOfMonth(p.payment_date) AS month,
    count() AS payment_count,
    round(sum(p.amount), 2) AS revenue
FROM helloworld.public_payment p
JOIN helloworld.public_staff st
    ON st.staff_id = p.staff_id
JOIN helloworld.public_store s
    ON s.store_id = st.store_id
WHERE
    p._peerdb_is_deleted = 0
    AND st._peerdb_is_deleted = 0
    AND s._peerdb_is_deleted = 0
GROUP BY
    s.store_id,
    month
ORDER BY
    month,
    s.store_id;

Category Mix by Revenue

SELECT
    cat.name AS category,
    countDistinct(r.rental_id) AS rentals,
    round(sum(p.amount), 2) AS revenue,
    round(revenue / rentals, 2) AS revenue_per_rental
FROM helloworld.public_payment p
JOIN helloworld.public_rental r
    ON r.rental_id = p.rental_id
JOIN helloworld.public_inventory i
    ON i.inventory_id = r.inventory_id
JOIN helloworld.public_film_category fc
    ON fc.film_id = i.film_id
JOIN helloworld.public_category cat
    ON cat.category_id = fc.category_id
WHERE
    p._peerdb_is_deleted = 0
    AND r._peerdb_is_deleted = 0
    AND i._peerdb_is_deleted = 0
    AND fc._peerdb_is_deleted = 0
    AND cat._peerdb_is_deleted = 0
GROUP BY cat.name
ORDER BY revenue DESC;

Actor by Popularity by Rental

SELECT
    a.actor_id,
    concat(a.first_name, ' ', a.last_name) AS actor_name,
    countDistinct(f.film_id) AS film_count,
    count(r.rental_id) AS rental_count
FROM helloworld.public_actor a
JOIN helloworld.public_film_actor fa
    ON fa.actor_id = a.actor_id
JOIN helloworld.public_film f
    ON f.film_id = fa.film_id
JOIN helloworld.public_inventory i
    ON i.film_id = f.film_id
JOIN helloworld.public_rental r
    ON r.inventory_id = i.inventory_id
WHERE
    a._peerdb_is_deleted = 0
    AND fa._peerdb_is_deleted = 0
    AND f._peerdb_is_deleted = 0
    AND i._peerdb_is_deleted = 0
    AND r._peerdb_is_deleted = 0
GROUP BY
    a.actor_id,
    actor_name
ORDER BY rental_count DESC
LIMIT 25;

Customer Cohort by first rental month

WITH first_rental AS
(
    SELECT
        customer_id,
        toStartOfMonth(min(rental_date)) AS cohort_month
    FROM helloworld.public_rental
    WHERE _peerdb_is_deleted = 0
    GROUP BY customer_id
)
SELECT
    fr.cohort_month,
    toStartOfMonth(r.rental_date) AS activity_month,
    dateDiff('month', fr.cohort_month, toStartOfMonth(r.rental_date)) AS cohort_age_months,
    countDistinct(r.customer_id) AS active_customers,
    count() AS rentals
FROM helloworld.public_rental r
JOIN first_rental fr
    ON fr.customer_id = r.customer_id
WHERE r._peerdb_is_deleted = 0
GROUP BY
    fr.cohort_month,
    activity_month,
    cohort_age_months
ORDER BY
    fr.cohort_month,
    cohort_age_months;

CDC Sanity Check

SELECT
    table,
    sum(rows) AS rows_on_disk
FROM system.parts
WHERE
    database = 'helloworld'
    AND active
    AND table LIKE 'public_%'
GROUP BY table
ORDER BY rows_on_disk DESC;

a kawaii DVD box holding some popcorn and wearing 3d glasses