Asked in: DOCUSIGN
Image of the Question
All Testcases Passed ✔
Solution
SELECT
u.email,
COUNT(*) AS total_transactions,
ROUND(SUM(t.amount), 2) AS total_amount
// ... rest of solution available after purchaseExplanation
```
To solve the problem of generating a user transaction report for the year 2023, we need to carefully think about how to aggregate transactional data per user and filter it correctly by date, as well as how to join user information with transaction data. The goal is to produce a report that lists each user’s email, the total number of transactions they made in 2023, and the total sum of those transactions formatted properly.
1. **Understanding the Data Model:**
We have two tables: `users` and `transactions`. Each transaction is linked to a user by `user_id`. The `users` table holds user-specific info like `email`, while the `transactions` table stores transactional info such as the `amount` and `created_at` timestamp of when the transaction occurred.
2. **Filtering by Year:**
Since the requirement is to include only transactions that happened in the year 2023, the first step is to filter the transactions based on the `created_at` column. The `created_at` is a string in the format `YYYY-MM-DD HH:MM:SS`, so to check if a transaction occurred in 2023, you can inspect the first four characters of this timestamp string to see if they equal "2023".
3. **Aggregating Transactions Per User:**
For each user, we want to compute two aggregates:
- The count of transactions that occurred in 2023.
- The sum of the amounts of these transactions, rounded and formatted to two decimal places with trailing zeros if necessary.
This means that for each user, we need to:
- Count the number of filtered transactions.
- Sum the `amount` column for those filtered transactions.
4. **Joining Users with Their Transactions:**
Because the transactions only contain the `user_id`, but the report requires user `email`, we need to join the `transactions` table with the `users` table. The join will be done on `transactions.user_id = users.id`.
5. **Handling Users Without Transactions:**
The problem doesn't explicitly specify whether to include users who have zero transactions in 2023. Typically, for reporting active transactions, only users who made transactions would be included. But if the requirement was to include all users even if they had zero transactions, the join would be an outer join and null handling for counts and sums would be required. Since the problem seems to focus on transactions, an inner join or filtering on transactions should suffice.
6. **Sorting the Output:**
The final results need to be sorted by `email` in ascending order. This means after the aggregation, the output should be ordered alphabetically by the user’s email address.
7. **Formatting the Output:**
- The total transaction count is an integer value.
- The total amount must be rounded to two decimal places. It should display trailing zeros if necessary, for example "500.00" instead of "500" or "500.0".
- This can typically be handled by formatting functions depending on the environment (e.g., SQL functions like `ROUND` or `FORMAT`).
8. **Overall Approach:**
- Start by joining the `users` and `transactions` tables on user ID.
- Filter the transactions so that only those created in 2023 remain.
- Group the joined result by user email.
- Compute the total count of transactions per user.
- Compute the total sum of transaction amounts per user, rounding and formatting it correctly.
- Sort the results by user email in ascending order.
- Output the resulting dataset with the three columns: `email`, `total_transactions`, and `total_amount`.
9. **Additional Notes:**
- The `created_at` column being a VARCHAR requires care in date filtering; extracting the year part via substring or equivalent is important.
- The amount column is DECIMAL(5,2), which means it already has two decimal places precision, but summing could increase decimal places, so formatting is necessary.
- If performance is a consideration (for large datasets), indexing on `created_at` or `user_id` columns could help speed filtering and joins.
In summary, this problem is primarily about filtering data by date, joining relational data across tables, aggregating numeric data per user, formatting output precisely, and sorting results correctly. It involves a methodical approach to query writing and data handling rather than algorithmic complexity. The final report helps to understand user engagement and spending patterns in 2023.
```