c.k c.k - 5 months ago 10
MySQL Question

Get all distinct users - MySql

I have a table contains the following type and information.

product_id | user_id | product | date_opened
1 | 10 | shoes | 2016-04-01
2 | 26 | shoes | 2016-04-01
3 | 10 | watch | 2016-04-01
4 | 23 | shoes | 2016-04-01
5 | 10 | shoes | 2016-01-01
6 | 13 | watch | 2016-01-01
7 | 14 | shoes | 2015-11-02
8 | 10 | slippers| 2015-11-02
9 | 10 | shoes | 2015-11-02
10 | 15 | watch | 2015-11-02


Users can buy products if it is open.
date_open
is the date cycle. users can buy as many product as he wants, example is
user_id
10, user 10 have 2 products on cycle 2016-04-01. One product on 2016-01-01, two on 2015-11-02.

Now, I would like to get all the (distinct) user_id who are active / have products on previous 3 cycle dates (2016-04-01, 2016-01-01 and 2015-11-02).

Note that a user can have many products in a cycle.

Additional:
Active - should consecutively have products on
dates
and without skipping.

Dates: From
2016-04-01
to down dates(3 consecutive dates).

Answer

This SQL gets you the distinct users that have products on the previous three cycle dates:

SELECT DISTINCT USER_ID
FROM   YOUR_TABLE
WHERE  DATE_OPENED IN ('2016-04-01', '2016-01-01', '2015-11-02');

In case your database does not know the DISTINCT command, you may use a GROUP BY clause as follows:

SELECT USER_ID
FROM   YOUR_TABLE
WHERE  DATE_OPENED IN ('2016-04-01', '2016-01-01', '2015-11-02')
GROUP BY USER_ID;