I have a table, Account, which I am querying to identify old accounts based on sales records. An account may have multiple rows in a Property table. A property may be closed. If all properties on an account are closed, the account should be returned as it is old.
How do I identify an account where all properties are closed?
Additionally, an account is considered old if they have not purchased a Service in 24 months.
As an example, given the following tables:
-- ACCOUNT --
+-------+-----------------------------------+
| ac_id | ac_email |
+-------+-----------------------------------+
| 1416 | bob@bob.com |
| 1419 | joe@joe.com |
+-------+-----------------------------------+
-- PROPERTY --
+------+---------------+-------+
| p_id | p_closed | ac_id |
+------+---------------+-------+
| 3 | FALSE | 1416 |
| 6 | TRUE | 1419 |
| 7 | TRUE | 1419 |
+------+---------------+-------+
--SERVICE--
+------+------------+
| p_id | s_saledate |
+------+------------+
| 3 | 2010-03-17 |
| 3 | 2011-02-16 |
| 6 | 2022-11-14 |
| 7 | 2022-01-24 |
+------+------------+
I would expect the following returned table
+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
| account_id | email | all_properties_closed | property_ids | latest_sale_date |
+------------+-----------------------+-----------------------+--------------+------------+--------------+------------------+
| 1416 | bob@bob.com | FALSE | 9 | 2011-02-16 |
| 1419 | joe@joe.com | TRUE | 6,7 | 2022-11-14 |
+------------+-----------------------+----------------+---------------------------+--------------+------------------+
Bob’s account is returned because he’s not had a Service in over 2 years & Joe’s account is returned because he has had a service in 2 years, but all of his properties are closed.
This is my current query
SELECT
ac_id as account_id,
GROUP_CONCAT(DISTINCT p_id) as property_ids,
MAX(sale_date) as latest_sale_date
FROM Account
JOIN Property USING (ac_id)
JOIN Service USING (p_id)
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31
I imagine I need to use the ALL operator and WHERE to get the all_properties_closed column, but I’m unsure how. Do I need a subquery?
>Solution :
You can compute all_properties_closed with conditional aggregation. Assuming that p_closed is of a string-like datatype, that would look like:
SELECT
ac_id as account_id,
MIN(p_closed = 'FALSE') all_properties_closed
GROUP_CONCAT(DISTINCT p_id) as property_ids,
MAX(sale_date) as latest_sale_date
FROM Account
JOIN Property USING (ac_id)
JOIN Service USING (p_id)
GROUP BY ac.ac_id
HAVING latest_sale_date < 2021-03-31 OR all_properties_closed
Expression MIN(p_closed = 'FALSE') yields 0 if any p_closed in the group has a non-'FALSE' value, else it returns 1; we can use this information to filter in the HAVING clause.
In normal usage you would have a 0/1 flag in column p_closed, and we could just do:
MIN(p_closed) all_properties_closed