0

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
1
  • WHERE GROUP_CONCAT(is_service_enabled ORDER BY enabled_date) = 'TRUE,FALSE,TRUE'
    – Akina
    Sep 20, 2022 at 4:37

1 Answer 1

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.