-
**********************************************************************************.
Table: fb_account_status
An account can have at most 2 rows: when it's created, and when it's closed. For active accounts, there is only one-row corresponding to when the account was first created.
col_name | col_type
-------------------+--------------------------
account_id | bigint
date | date
status | varchar(10) -- 'created', 'closed'
Sample results
closed_percentage --------------------- 33.3333333333333333
Solution postgres
SELECT COUNT(DISTINCT CASE WHEN status = 'closed' THEN account_id ELSE NULL END) * 100.0/
COUNT(DISTINCT account_id) As closed_percentage
FROM fb_account_status;
Explanation
This query is calculating the percentage of accounts that are closed in a table called "fb_account_status".
The query uses the COUNT function to count the number of distinct account IDs that have a status of 'closed'. The CASE statement is used to only count the account IDs that have a status of 'closed'. If the status is not 'closed', the function returns NULL.
The query also uses the COUNT function to count the total number of distinct account IDs in the table.
The two counts are then divided, and the result is multiplied by 100 to get the percentage of accounts that are closed.
The final result is returned as a column named "closed_percentage".
Expected results