Mysql refrencing derived tables from nested query

Question

I posted something similar to this yesterday, but now I'd like something a little different from my query-

I'm trying to query a database to retrieve the number of one-time users who have visited a website over time. The data looks something like this:

Day | UserID  
  1 | A  
  1 | B  
  2 | B  
  3 | A  
  4 | B  
  4 | C  
  5 | D  

I'd like the query result to look this this

Time Span      | COUNT(DISTINCT UserID)  
Day 1 to Day 1 | 2  
Day 1 to Day 2 | 1  
Day 1 to Day 3 | 0  
Day 1 to Day 4 | 1  
Day 1 to Day 5 | 2  

The result is 2,1,0,1,2 because, at the end of those days, there are X number of users who have visited a single time. e.g. for day 5, at the end of day 5, users c and d have visited only once each.

I think I'm looking for a query similar to this:

select d.day, (select count(distinct userid) from visits where day<=d.day)
from (select distinct day from visits) d

The difference between the query above and what I'm looking for is that I'd like this new query to consider only one-time users for each time span, and not repeat users.

Thanks

Answer 1

This subquery should work for the clarified requirements.

select d.day, count(distinct case when b.userid is null then a.userid end)
from (select day from visits group by day) d
inner join
(
select a.day, a.userid, count(*) c
from visits a
join visits b on a.userid=b.userid and b.day <= a.day
group by a.day, a.userid
having count(*) = 1
) a on a.day <= d.day
left join
(
select a.day, a.userid, count(*) c
from visits a
join visits b on a.userid=b.userid and b.day <= a.day
group by a.day, a.userid
having count(*) > 1
) b on a.userid = b.userid and b.day <= d.day
group by d.day

Original

You must have taken the idea from SQL Server - it is the only RDBMS (IIRC) that will allow you to reference a twice removed (nesting) query. Please indicate what you want and we can rewrite the query.

For the exact query shown, you don't need 2 levels of subquery

SELECT  
    C.col_c1 AS Data,
    (
        SELECT count(col_b1)
        FROM tbl
        WHERE col_b2 <= C.col_c1
    ) A
FROM (
    SELECT col_c1 # subquery to get distinct c1
    FROM tbl
    GROUP BY col_c1) C;