Search⌘ K
AI Features
Home
Courses
Grokking the Data Science Interview

Joins Deep Dive

Explore how to accurately calculate monthly revenue per active user using SQL joins. Understand the importance of maintaining data granularity to prevent duplications that inflate revenue. Learn techniques to validate join results, handle unmatched rows with LEFT JOIN, and apply correct join conditions including self joins and full outer joins. This lesson teaches how to detect and correct common join errors that affect revenue and user activity analysis in data science interviews.

We'll cover the following...

The interviewer asks, “Calculate monthly revenue per active user.” This lesson uses three tables: users, with one row per user; orders, with one row per order and columns for user_id, amount, and created_at; and events, which records user activity. An incorrect approach joins orders to events on user_id, groups the results by month, and divides SUM(o.amount) by the number of active users. The join matches records by user_id but can duplicate order rows when a user has multiple events, inflating the revenue total. If a user has one $30 order and two matching January events, the join produces two rows containing the same order amount, causing that order to contribute $60 to the revenue total. Starting from orders and using an INNER JOIN with events also excludes active users who have no matching orders, potentially understating the active-user denominator.

The resulting calculation error may not be immediately apparent. January revenue totals $60 across two active users, resulting in $30 in revenue per active user. The joined query incorrectly reports $60 per active user because each order matches two event rows, doubling the aggregated revenue from $60 to $120. Because SUM() includes the duplicated order amounts, the query returns an inflated metric without producing a SQL execution error.

An effective approach calculates revenue and active-user counts separately. The orders table contains revenue data at the order level, with one row per order_id. Deduplicate the events table to obtain one row per active user_id per month. Aggregate each dataset to the appropriate monthly granularity before joining, or calculate revenue and active-user counts separately and join the monthly results.

python310
import sqlite3
def money(cents: int) -> str:
    return f"${cents / 100:.2f}"
def main() -> None:
    con = sqlite3.connect(":memory:")
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    cur.executescript(
        """
        CREATE TABLE users (
          user_id INTEGER PRIMARY KEY
        );
        CREATE TABLE orders (
          order_id INTEGER PRIMARY KEY,
          user_id INTEGER NOT NULL,
          amount_cents INTEGER NOT NULL,
          created_at TEXT NOT NULL
        );
        CREATE TABLE events (
          event_id INTEGER PRIMARY KEY,
          user_id INTEGER NOT NULL,
          event_name TEXT NOT NULL,
          created_at TEXT NOT NULL
        );
        """
    )
    cur.executemany("INSERT INTO users(user_id) VALUES (?)", [(1,), (2,), (3,)])
    cur.executemany(
        "INSERT INTO orders(order_id, user_id, amount_cents, created_at) VALUES (?, ?, ?, ?)",
        [
            (100, 1, 3000, "2024-01-05"),
            (101, 2, 3000, "2024-01-10"),
        ],
    )
    cur.executemany(
        "INSERT INTO events(event_id, user_id, event_name, created_at) VALUES (?, ?, ?, ?)",
        [
            (200, 1, "app_open", "2024-01-02"),
            (201, 1, "purchase_click", "2024-01-03"),
            (202, 2, "app_open", "2024-01-04"),
            (203, 2, "view_product", "2024-01-07"),
        ],
    )
    flawed = cur.execute(
        """
        SELECT
          strftime('%Y-%m-01', o.created_at) AS month,
          SUM(o.amount_cents) AS revenue_cents,
          COUNT(DISTINCT e.user_id) AS active_users,
          CAST(SUM(o.amount_cents) AS REAL) / COUNT(DISTINCT e.user_id) AS revenue_per_active_user
        FROM orders AS o
        JOIN events AS e
          ON e.user_id = o.user_id
         AND strftime('%Y-%m', e.created_at) = strftime('%Y-%m', o.created_at)
        GROUP BY 1
        ORDER BY 1;
        """
    ).fetchone()
    print("Flawed join")
    print(f"  month: {flawed['month']}")
    print(f"  revenue: {money(flawed['revenue_cents'])}")
    print(f"  revenue per active user: {money(int(round(flawed['revenue_per_active_user'])))}")
    print()
    baseline = cur.execute(
        """
        WITH revenue_by_month AS (
          SELECT
            strftime('%Y-%m-01', created_at) AS month,
            SUM(amount_cents) AS revenue_cents
          FROM orders
          GROUP BY 1
        ),
        active_users_by_month AS (
          SELECT
            strftime('%Y-%m-01', created_at) AS month,
            COUNT(DISTINCT user_id) AS active_users
          FROM events
          GROUP BY 1
        )
        SELECT
          r.month,
          r.revenue_cents,
          a.active_users,
          CAST(r.revenue_cents AS REAL) / a.active_users AS revenue_per_active_user
        FROM revenue_by_month AS r
        JOIN active_users_by_month AS a
          ON a.month = r.month
        ORDER BY r.month;
        """
    ).fetchone()
    print("Correct baseline")
    print(f"  month: {baseline['month']}")
    print(f"  revenue: {money(baseline['revenue_cents'])}")
    print(f"  revenue per active user: {money(int(round(baseline['revenue_per_active_user'])))}")
if __name__ == "__main__":
    main()

  • Lines 55–69: The join matches each order to every event the same user generated in the same ...