Monthly Transactions I - Leetcode Solution

Problem Statement

The Monthly Transactions I problem on LeetCode is a database problem. You are given a table with the following columns:

• id: A unique identifier for each record.
• month: The month in which the transaction took place (in the format yyyy-mm).
• recurring: A binary value indicating if the transaction is recurring (1) or not (0).
• amount: The amount of the transaction.

Your task is to write a SQL query to:

• Calculate the total amount of recurring transactions for each month, for all months in the table.
• Calculate the total amount of non-recurring transactions for each month, for all months in the table.
• Calculate the difference between the total amount of recurring and non-recurring transactions for each month, for all months in the table.

Solution

To solve this problem, we need to calculate the three metrics - total amount of recurring transactions, total amount of non-recurring transactions, and the difference between them - for each month in the table.

Let's start by calculating the total amount of recurring transactions for each month. We can do this by grouping the table by month, filtering for recurring transactions, and then summing the amount column. Here's the SQL query for this:

SELECT month, SUM(amount) AS recurring_total
FROM transactions
WHERE recurring = 1
GROUP BY month

Next, we need to calculate the total amount of non-recurring transactions for each month. We can do this by grouping the table by month, filtering for non-recurring transactions, and then summing the amount column. Here's the SQL query for this:

SELECT month, SUM(amount) AS non_recurring_total
FROM transactions
WHERE recurring = 0
GROUP BY month

Finally, we need to calculate the difference between the total amount of recurring and non-recurring transactions for each month. We can do this by joining the two previous queries on the month column, and subtracting the non-recurring total from the recurring total. Here's the SQL query for this:

SELECT r.month, r.recurring_total, n.non_recurring_total, r.recurring_total - n.non_recurring_total AS difference
FROM (SELECT month, SUM(amount) AS recurring_total
      FROM transactions
      WHERE recurring = 1
      GROUP BY month) AS r
JOIN (SELECT month, SUM(amount) AS non_recurring_total
      FROM transactions
      WHERE recurring = 0
      GROUP BY month) AS n
ON r.month = n.month

This query will give us the three metrics - total amount of recurring transactions, total amount of non-recurring transactions, and the difference between them - for each month in the table.

1