Finding the latest subscription date

Question

I need to query a users database that contains record based on their subscription to a service. A user can have subscribed to it while buying the main product or have it added to their subscription later on. I need to find the latest date it was subscribed. There may be a scenario where the user has enabled the add-on only to have it disabled and enabled again. In this case, the second instance it was enabled should show up in the results. For eg: a sample data is given below. I should get only the following output for firm_id:100

|100 | TRUE | 18/09/2022 |

How do I go about this?

user_id	is_service_enabled	enabled_date
101	FALSE	12/02/2022
102	TRUE	07/06/2021
101	TRUE	12/04/2022
100	TRUE	10/07/2021
100	FALSE	10/09/2021
104	FALSE	07/09/2022
100	TRUE	18/09/2022

Answer 1

PROPOSED QUERY

SELECT B.* FROM
(SELECT user_id,MAX(enabled_date) maxdate FROM mytable GROUP BY user_id) A
INNER JOIN mytable B ON A.user_id=B.user_id AND A.maxdate=B.enabled_date
ORDER BY B.user_id;

What you need is a query that gets the maximum enabled_date for each user_id and then join that result back to the same table.

YOUR SAMPLE DATA LOADED

USE ashishthakur
CREATE TABLE mytable
(
    user_id INT,
    is_service_enabled VARCHAR(10),
    enabled_date DATE
);
INSERT INTO mytable (user_id,is_service_enabled,enabled_date) VALUES
(101,'FALSE','2022-02-12'),
(102,'TRUE' ,'2021-06-07'),
(101,'TRUE' ,'2022-04-12'),
(100,'TRUE' ,'2021-07-10'),
(100,'FALSE','2021-09-10'),
(104,'FALSE','2022-09-07'),
(100,'TRUE' ,'2022-09-18');
SELECT * FROM mytable;
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     user_id INT,
    ->     is_service_enabled VARCHAR(10),
    ->     enabled_date DATE
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO mytable (user_id,is_service_enabled,enabled_date) VALUES
    -> (101,'FALSE','2022-02-12'),
    -> (102,'TRUE' ,'2021-06-07'),
    -> (101,'TRUE' ,'2022-04-12'),
    -> (100,'TRUE' ,'2021-07-10'),
    -> (100,'FALSE','2021-09-10'),
    -> (104,'FALSE','2022-09-07'),
    -> (100,'TRUE' ,'2022-09-18');
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+---------+--------------------+--------------+
| user_id | is_service_enabled | enabled_date |
+---------+--------------------+--------------+
|     101 | FALSE              | 2022-02-12   |
|     102 | TRUE               | 2021-06-07   |
|     101 | TRUE               | 2022-04-12   |
|     100 | TRUE               | 2021-07-10   |
|     100 | FALSE              | 2021-09-10   |
|     104 | FALSE              | 2022-09-07   |
|     100 | TRUE               | 2022-09-18   |
+---------+--------------------+--------------+
7 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

mysql> SELECT B.* FROM
    -> (SELECT user_id,MAX(enabled_date) maxdate FROM mytable GROUP BY user_id) A
    -> INNER JOIN mytable B ON A.user_id=B.user_id AND A.maxdate=B.enabled_date
    -> ORDER BY B.user_id;
+---------+--------------------+--------------+
| user_id | is_service_enabled | enabled_date |
+---------+--------------------+--------------+
|     100 | TRUE               | 2022-09-18   |
|     101 | TRUE               | 2022-04-12   |
|     102 | TRUE               | 2021-06-07   |
|     104 | FALSE              | 2022-09-07   |
+---------+--------------------+--------------+
4 rows in set (0.00 sec)

mysql>