SQL : number different dates within users

Question

I know this question must have been asked before but I can't find it when I am looking around. Basically I have a table with column 1 and 2, and I want to know how I can construct column 3 of this table:

user   date         number
---------------------------
 1     2017-08-22     1
 1     2017-08-22     1
 1     2017-09-12     2
 1     2017-09-12     2
 1     2017-10-15     3
 2     2017-05-23     1
 2     2017-07-24     2

Anyone got an idea?

Which dbms are you using? (The answer will perhaps be product specific.) — jarlh, Oct 19 '17 at 09:41
does not seem like it does @jarlh, but I found this now: https://stackoverflow.com/questions/3333665/rank-function-in-mysql which is hopefully enough to solve the problem — digestivee, Oct 19 '17 at 09:45
Subquery might work: _SELECT et.user, et.date, (SELECT COUNT(DISTINCT date) FROM exampleTable WHERE user = et.user AND date <= et.date) AS number FROM exampleTable AS et ORDER BY user ASC;_ @TrotteBoman — Magisch, Oct 19 '17 at 09:46
That seems to produce something like what I wanted. I will experiment with it a little further but I think you might have got it @Magisch — digestivee, Oct 19 '17 at 09:52

Magisch · Accepted Answer · 2017-10-19T09:58:52.903

Other then Rownumber shenanigans a subquery might do the trick:

SELECT
et.user,
et.date,
(SELECT COUNT(DISTINCT date) FROM exampleTable WHERE user = et.user AND date <= et.date) AS number
FROM exampleTable AS et 
ORDER BY user ASC;

This works by making number the amount of occurences of distinct dates lower then or equal to the current date.

Detailed breakdown:

You're trying to "rank" the dates for any given user.
Therefore multiple occurences of one date aren't counted (COUNT(DISTINCT))
The rank of each row is the amount of unique dates lower then or equal to it that came before

You can try it out online here

SQL : number different dates within users

1 Answers1