mirror of
https://gitee.com/coder-xiaomo/leetcode-problemset
synced 2025-01-10 18:48:13 +08:00
49 lines
2.0 KiB
HTML
49 lines
2.0 KiB
HTML
<p>Table: <code>Transactions</code></p>
|
|
|
|
<pre>
|
|
+---------------+---------+
|
|
| Column Name | Type |
|
|
+---------------+---------+
|
|
| id | int |
|
|
| country | varchar |
|
|
| state | enum |
|
|
| amount | int |
|
|
| trans_date | date |
|
|
+---------------+---------+
|
|
id is the primary key of this table.
|
|
The table has information about incoming transactions.
|
|
The state column is an enum of type ["approved", "declined"].
|
|
</pre>
|
|
|
|
<p> </p>
|
|
|
|
<p>Write an SQL query to find for each month and country, the number of transactions and their total amount, the number of approved transactions and their total amount.</p>
|
|
|
|
<p>Return the result table in <strong>any order</strong>.</p>
|
|
|
|
<p>The query result format is in the following example.</p>
|
|
|
|
<p> </p>
|
|
<p><strong class="example">Example 1:</strong></p>
|
|
|
|
<pre>
|
|
<strong>Input:</strong>
|
|
Transactions table:
|
|
+------+---------+----------+--------+------------+
|
|
| id | country | state | amount | trans_date |
|
|
+------+---------+----------+--------+------------+
|
|
| 121 | US | approved | 1000 | 2018-12-18 |
|
|
| 122 | US | declined | 2000 | 2018-12-19 |
|
|
| 123 | US | approved | 2000 | 2019-01-01 |
|
|
| 124 | DE | approved | 2000 | 2019-01-07 |
|
|
+------+---------+----------+--------+------------+
|
|
<strong>Output:</strong>
|
|
+----------+---------+-------------+----------------+--------------------+-----------------------+
|
|
| month | country | trans_count | approved_count | trans_total_amount | approved_total_amount |
|
|
+----------+---------+-------------+----------------+--------------------+-----------------------+
|
|
| 2018-12 | US | 2 | 1 | 3000 | 1000 |
|
|
| 2019-01 | US | 1 | 1 | 2000 | 2000 |
|
|
| 2019-01 | DE | 1 | 1 | 2000 | 2000 |
|
|
+----------+---------+-------------+----------------+--------------------+-----------------------+
|
|
</pre>
|