- **************************************************************************************************************
- ***************************************************
- *************************************************************************
Table 1: affirm_account
Bank account creation/deletion records, the account type can only be one of 'checking', 'saving', or 'both'.
col_name | col_type ---------------+------------------- id | bigint account_type | varchar (20) -- 'checking', 'saving', or 'both action | varchar (10) -- 'created' or 'deleted' date | date
Table 2: affirm_account_detail
Bank account and meta data.
col_name | col_type ---------------+------------------- account_id | bigint city | varchar (100) state | varchar (100) country | varchar (2)
Sample results
country | mom_growth ---------+--------------------- CA | 50.0000000000000000 US | 28.5714285714285714 UK | 25.0000000000000000
Solution postgres
WITH july AS (
SELECT D.country, COUNT(DISTINCT A.id) AS num_new_accounts
FROM affirm_account A
INNER JOIN affirm_account_detail D
ON D.account_id = A.id
WHERE action = 'created'
AND date BETWEEN '2021-07-01'
AND '2021-07-31'
GROUP BY D.country
),
august AS (
SELECT D.country, COUNT(DISTINCT A.id) AS num_new_accounts
FROM affirm_account A
INNER JOIN affirm_account_detail D
ON D.account_id = A.id
WHERE action = 'created'
AND date BETWEEN '2021-08-01'
AND '2021-08-31'
GROUP BY D.country
)
SELECT A.country, COALESCE(A.num_new_accounts, 0) * 100.0 / J.num_new_accounts AS mom_growth
FROM july J
LEFT JOIN august A
ON A.country = J.country
ORDER BY mom_growth DESC LIMIT 3;
Explanation
This query is looking at the number of new accounts created in July and August for each country in the affirm_account_detail table. It is filtering for only the "created" action and selecting the count of unique account IDs.
The query then calculates the month-over-month growth rate (mom_growth) for each country by dividing the number of new accounts in August by the number of new accounts in July, and multiplying by 100. If there were no new accounts in August for a particular country, the COALESCE function ensures that the query returns a value of 0 for mom_growth.
Finally, the query selects the top 3 countries with the highest mom_growth, and orders them in descending order by mom_growth.
Expected results
More Affirm questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 130 | Newly created accounts affirm | easy |
|
| 131 | Churned accounts affirm | hard |
|