Number of Transactions per Visit - Practice Coding Problems

Number of Transactions per Visit - Problem

Database Hard

Imagine you're working for a bank's analytics team, and you need to create a transaction frequency histogram to understand customer behavior patterns during visits.

You have access to two tables:

Visits: Records every time a user visits the bank (user_id, visit_date)
Transactions: Records every transaction made (user_id, transaction_date, amount)

Your task is to analyze how many transactions customers typically make per visit. The bank wants a complete picture showing:

How many customers made 0 transactions during their visit
How many customers made 1 transaction during their visit
How many customers made 2 transactions during their visit
And so on, up to the maximum number of transactions made by any customer in a single visit

The result should be a histogram-style table with transactions_count (0 to max) and visits_count (number of customers who made that many transactions), ordered by transaction count.

Key Challenge: You must include all values from 0 to the maximum, even if no customers made that exact number of transactions (show 0 for visits_count in such cases).

Input & Output

example_1.sql — Basic Case

$ Input: Visits: | user_id | visit_date | |---------|------------| | 1 | 2020-01-01 | | 2 | 2020-01-02 | | 12 | 2020-01-01 | | 19 | 2020-01-03 | | 1 | 2020-01-02 | | 2 | 2020-01-03 | | 1 | 2020-01-04 | Transactions: | user_id | transaction_date | amount | |---------|------------------|--------| | 1 | 2020-01-02 | 120 | | 2 | 2020-01-03 | 22 | | 2 | 2020-01-03 | 33 | | 19 | 2020-01-03 | 10 |

› Output: | transactions_count | visits_count | |--------------------|---------------| | 0 | 4 | | 1 | 2 | | 2 | 1 |

💡 Note: User 1 has 3 visits: 01-01 (0 trans), 01-02 (1 trans), 01-04 (0 trans). User 2 has 2 visits: 01-02 (0 trans), 01-03 (2 trans). User 12 has 1 visit: 01-01 (0 trans). User 19 has 1 visit: 01-03 (1 trans). So we have 4 visits with 0 transactions, 2 visits with 1 transaction, and 1 visit with 2 transactions.

example_2.sql — All Zero Transactions

$ Input: Visits: | user_id | visit_date | |---------|------------| | 1 | 2020-01-01 | | 2 | 2020-01-02 | | 3 | 2020-01-03 | Transactions: (empty table)

› Output: | transactions_count | visits_count | |--------------------|---------------| | 0 | 3 |

💡 Note: All 3 users visited but made no transactions, so all visits have 0 transactions. The result only shows transactions_count = 0 with visits_count = 3.

example_3.sql — High Transaction Count

$ Input: Visits: | user_id | visit_date | |---------|------------| | 1 | 2020-01-01 | | 1 | 2020-01-02 | Transactions: | user_id | transaction_date | amount | |---------|------------------|--------| | 1 | 2020-01-01 | 100 | | 1 | 2020-01-01 | 200 | | 1 | 2020-01-01 | 300 | | 1 | 2020-01-01 | 400 | | 1 | 2020-01-01 | 500 |

› Output: | transactions_count | visits_count | |--------------------|---------------| | 0 | 1 | | 1 | 0 | | 2 | 0 | | 3 | 0 | | 4 | 0 | | 5 | 1 |

💡 Note: User 1 made 2 visits: 01-01 with 5 transactions and 01-02 with 0 transactions. The result must show all counts from 0 to 5, even though counts 1-4 have zero visits.

Visualization

Tap to expand

Understanding the Visualization

Collect Visit Data

Every customer visit is recorded with date

Track Transactions

Each transaction links to a specific visit

Count Per Visit

Group transactions by user and visit date

Create Histogram

Generate complete frequency distribution

Fill Gaps

Include all counts from 0 to maximum, even if zero visits

Key Takeaway

🎯 Key Insight: Use CTEs with LEFT JOIN and range generation to create a complete histogram without gaps, ensuring every possible transaction count from 0 to maximum is represented in the final result.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through visits and transactions data with efficient aggregation and range generation.

✓ Linear Growth

Space Complexity

O(m)

Where m is the maximum transaction count - only stores the final histogram results.

✓ Linear Space

Constraints

1 ≤ Number of visits ≤ 10⁴
1 ≤ Number of transactions ≤ 10⁴
1 ≤ user_id ≤ 100
All transaction dates are guaranteed to have corresponding visit records
The result must include all values from 0 to MAX(transactions_per_visit)

Asked in

a Amazon 42 G Google 38 f Meta 35 ⊞ Microsoft 29 GS Goldman Sachs 25

This SQL problem requires creating a complete transaction frequency histogram using CTEs and LEFT JOINs. The optimal approach uses a recursive CTE to generate the complete range (0 to MAX) and efficiently aggregates transaction counts per visit in a single pass. Key insight: must include all possible transaction counts, even those with zero visits, making this a classic gap-filling SQL challenge.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized Single CTE (Optimal)	O(n)	O(m)	Single comprehensive CTE that calculates everything in one optimized pass
Brute Force (Nested Subqueries)	O(n×m)	O(1)	Use multiple nested queries to manually count each transaction count level
Two-Pass CTE Approach	O(n + m)	O(n)	First pass to count transactions per visit, second pass to generate complete histogram

Optimized Single CTE (Optimal) — Algorithm Steps

Single CTE to count transactions per visit with LEFT JOIN
Use window functions to find MAX transaction count
Generate complete range using RECURSIVE CTE or database-specific functions
Aggregate and fill gaps in single query execution
Return ordered complete histogram

Visualization

Tap to expand

Step-by-Step Walkthrough

Combined JOIN & COUNT

Single operation to get transaction counts per visit

Simultaneous Aggregation

Group by transaction count while finding MAX

Range Generation

Use recursive CTE to generate 0-to-max range

Final Result

Left join and output complete histogram

Code -

solution.c — C

// Optimal C implementation using libpq
/*
#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

const char* optimal_query = 
    "WITH RECURSIVE transaction_counts AS ("
    "    SELECT v.user_id, v.visit_date,"
    "           COUNT(t.transaction_date) as trans_count"
    "    FROM Visits v"
    "    LEFT JOIN Transactions t ON v.user_id = t.user_id "
    "                              AND v.visit_date = t.transaction_date"
    "    GROUP BY v.user_id, v.visit_date"
    "),"
    "histogram AS ("
    "    SELECT trans_count as transactions_count,"
    "           COUNT(*) as visits_count"
    "    FROM transaction_counts"
    "    GROUP BY trans_count"
    "),"
    "max_count AS ("
    "    SELECT MAX(transactions_count) as max_val FROM histogram"
    "),"
    "range_recursive AS ("
    "    SELECT 0 as transactions_count"
    "    UNION ALL"
    "    SELECT transactions_count + 1"
    "    FROM range_recursive, max_count"
    "    WHERE transactions_count < max_val"
    ")"
    "SELECT rr.transactions_count,"
    "       COALESCE(h.visits_count, 0) as visits_count"
    "FROM range_recursive rr"
    "LEFT JOIN histogram h ON rr.transactions_count = h.transactions_count"
    "ORDER BY rr.transactions_count;";

int execute_optimal_solution(PGconn *conn) {
    PGresult *result = PQexec(conn, optimal_query);
    
    if (PQresultStatus(result) != PGRES_TUPLES_OK) {
        fprintf(stderr, "Query failed: %s\n", PQerrorMessage(conn));
        PQclear(result);
        return 1;
    }
    
    int rows = PQntuples(result);
    for (int i = 0; i < rows; i++) {
        printf("%s,%s\n", PQgetvalue(result, i, 0), PQgetvalue(result, i, 1));
    }
    
    PQclear(result);
    return 0;
}
*/

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through visits and transactions data with efficient aggregation and range generation.

✓ Linear Growth

Space Complexity

O(m)

Where m is the maximum transaction count - only stores the final histogram results.

✓ Linear Space

Constraints

1 ≤ Number of visits ≤ 10⁴
1 ≤ Number of transactions ≤ 10⁴
1 ≤ user_id ≤ 100
All transaction dates are guaranteed to have corresponding visit records
The result must include all values from 0 to MAX(transactions_per_visit)

91.2K Views

High Frequency

~35 min Avg. Time

2.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Optimized Single CTE (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler