SQLPad
Sign In

131. Churned accounts

hard affirm
  • *******************************************************************
  • *******************************************************************
  • ************************************************************************************
  • *****************************************************************************************************************************************************************************************************************

Table: 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

Sample results 

 count
-------
     5
(1 row)

Solution postgres

WITH deleted_single_account AS
(
    SELECT id, account_type
    FROM affirm_account
    WHERE action='created'
    AND id IN (
            SELECT DISTINCT id
        FROM affirm_account
        WHERE action= 'deleted'
        AND date BETWEEN '2021-08-01' AND '2021-08-31'
    )
    AND account_type IN ('checking', 'saving')
),

potential_deleted_both_account AS (
    SELECT id, account_type
    FROM affirm_account
    WHERE action='created'
    AND id IN (
            SELECT DISTINCT id
        FROM affirm_account
        WHERE action= 'deleted'
        AND date BETWEEN '2021-08-01' AND '2021-08-31'
    )
    AND account_type = 'both'
),
deleted_both_account AS (
    SELECT id
    FROM affirm_account
    WHERE ID IN (
        SELECT id
        FROM potential_deleted_both_account
    )
    AND action='deleted'
    GROUP BY id
    HAVING COUNT(*) = 2 -- Deleted both 'checking' and 'saving' account;
)

SELECT COUNT(*) FROM (
    SELECT id
    FROM deleted_single_account
    UNION
    SELECT id
    FROM deleted_both_account
) X;

Explanation

This query is trying to identify the number of accounts that were created and then deleted between August 1-31, 2021.

The query selects the IDs and account types of all accounts that were created and then deleted within the specified date range. It separates these accounts into two groups: deleted_single_account, which includes accounts that were either checking or savings accounts, and potential_deleted_both_account, which includes accounts that were both checking and savings accounts.

The query then creates a third group, deleted_both_account, which only includes the IDs of potential_deleted_both_account that have been deleted twice (meaning both the checking and savings accounts were deleted).

Finally, the query combines the IDs of deleted_single_account and deleted_both_account, and counts the total number of IDs in the combined list, which represents the total number of accounts that were created and then deleted during the specified date range.

Expected results


More Affirm questions

ID Title Level FTPR
130 Newly created accounts affirm easy
37%
132 Top 3 trending countries affirm medium
9%