1336 Number of Transactions per Visit

Description

Table: Visits

• (user_id, visit_date) is the primary key (combination of columns with unique values) for this table.
• Each row of this table indicates that user_id has visited the bank in visit_date.

Table: Transactions

• This table may contain duplicates rows.
• Each row of this table indicates that user_id has done a transaction of amount in transaction_date.
• It is guaranteed that the user has visited the bank in the transaction_date.(i.e The Visits table contains (user_id, transaction_date) in one row)

Problem Statement

A bank wants to draw a chart of the number of transactions bank visitors did in one visit to the bank and the corresponding number of visitors who have done this number of transaction in one visit.

Write a solution to find how many users visited the bank and didn’t do any transactions, how many visited the bank and did one transaction, and so on.

The result table will contain two columns:

• transactions_count which is the number of transactions done in one visit.
• visits_count which is the corresponding number of users who did transactions_count in one visit to the bank.
• transactions_count should take all values from 0 to max(transactions_count) done by one or more users.

Return the result table ordered by transactions_count.

The result format is in the following example.

Example 1:

Input:

• Visits table:

• Transactions table:

Output:

Explanation:

The chart drawn for this example is shown above.

• For transactions_count = 0, The visits (1, "2020-01-01"), (2, "2020-01-02"), (12, "2020-01-01") and (19, "2020-01-03") did no transactions so visits_count = 4.
• For transactions_count = 1, The visits (2, "2020-01-03"), (7, "2020-01-11"), (8, "2020-01-28"), (1, "2020-01-02") and (1, "2020-01-04") did one transaction so visits_count = 5.
• For transactions_count = 2, No customers visited the bank and did two transactions so visits_count = 0.
• For transactions_count = 3, The visit (9, "2020-01-25") did three transactions so visits_count = 1.
• For transactions_count >= 4, No customers visited the bank and did more than three transactions so we will stop at transactions_count = 3

Solution

In this, we want to find the visits_count. So, we first need to join Visits table to Transactions table using LEFT OUTER JOIN. This way we do not lose any visit information.

1SELECT v.user_id, v.visit_date, t.amount
2    FROM Visits v
3    LEFT OUTER JOIN Transactions t ON (v.user_id = t.user_id AND v.visit_date = t.transaction_date)
4    ORDER BY v.visit_date, v.user_id;

Next, you try to calculate the number of visits per user_id and per visit_date.

1SELECT v.visit_date, v.user_id, 
2    SUM(CASE WHEN t.amount > 0 THEN 1 ELSE 0 END) AS amount
3    FROM Visits v
4    LEFT OUTER JOIN Transactions t ON (v.user_id = t.user_id AND v.visit_date = t.transaction_date)
5    GROUP BY v.visit_date, v.user_id
6    ORDER BY v.visit_date, v.user_id;

From this results, you can create reverse view by grouping by non_zero_amount and counting the number of unique user_id per visit_date.

 1WITH transactions_per_date_per_user AS (
 2   SELECT v.visit_date, v.user_id, 
 3       SUM(CASE WHEN t.amount > 0 THEN 1 ELSE 0 END) AS amount
 4       FROM Visits v
 5       LEFT OUTER JOIN Transactions t ON (v.user_id = t.user_id AND v.visit_date = t.transaction_date)
 6       GROUP BY v.visit_date, v.user_id
 7), counts_per_transactions_count AS (
 8   SELECT amount AS transactions_count,
 9       SUM(1) AS visits_count
10       FROM transactions_per_date_per_user
11       GROUP BY transactions_count
12) SELECT *
13   FROM counts_per_transactions_count;

Here, you’re missing rows where transactions_count had no visits_count. For example, it’s missing for transactions_count = 2. For this, you essentially need to generate continuous numbers from 0 to MAX(transactions_count). This can be done using ROW_NUMBER() function. When you generate row numbers using ROW_NUMBER() function, the numbers are generated from 1 onwards. So, you need to add 0 to this using UNION operation.

1SELECT ROW_NUMBER() OVER () AS rn
2    FROM Transactions
3    UNION SELECT 0;

Next, you can outer join this generated sequence with the results of the previous query.

 1WITH transactions_per_date_per_user AS (
 2    SELECT v.visit_date, v.user_id, 
 3        SUM(CASE WHEN t.amount > 0 THEN 1 ELSE 0 END) AS amount
 4        FROM Visits v
 5        LEFT OUTER JOIN Transactions t ON (v.user_id = t.user_id AND v.visit_date = t.transaction_date)
 6        GROUP BY v.visit_date, v.user_id
 7), counts_per_transactions_count AS (
 8    SELECT amount AS transactions_count,
 9        SUM(1) AS visits_count
10        FROM transactions_per_date_per_user
11        GROUP BY transactions_count
12), row_numbers AS (
13    SELECT ROW_NUMBER() OVER () AS rn
14        FROM Transactions
15        UNION SELECT 0 -- 0 rn is missing
16) SELECT rn AS transactions_count, visits_count AS visits_count
17    FROM row_numbers r
18    LEFT OUTER JOIN counts_per_transactions_count cptc ON cptc.transactions_count = r.rn
19    WHERE rn <= (SELECT MAX(transactions_count) FROM  counts_per_transactions_count)
20    ORDER BY transactions_count;

This gets us closer to the result we need except that we still have null value for visits_count. This can be solved by using IFNULL() function.

 1WITH transactions_per_date_per_user AS (
 2    SELECT v.visit_date, v.user_id, 
 3        SUM(CASE WHEN t.amount > 0 THEN 1 ELSE 0 END) AS amount
 4        FROM Visits v
 5        LEFT OUTER JOIN Transactions t ON (v.user_id = t.user_id AND v.visit_date = t.transaction_date)
 6        GROUP BY v.visit_date, v.user_id
 7), counts_per_transactions_count AS (
 8    SELECT amount AS transactions_count,
 9        SUM(1) AS visits_count
10        FROM transactions_per_date_per_user
11        GROUP BY transactions_count
12), row_numbers AS (
13    SELECT ROW_NUMBER() OVER () AS rn
14        FROM Transactions
15        UNION SELECT 0 -- 0 rn is missing
16) SELECT rn AS transactions_count, IFNULL(visits_count, 0) AS visits_count
17    FROM row_numbers r
18    LEFT OUTER JOIN counts_per_transactions_count cptc ON cptc.transactions_count = r.rn
19    WHERE rn <= (SELECT MAX(transactions_count) FROM  counts_per_transactions_count)
20    ORDER BY transactions_count;