Discussion on: A Second Walk Through of Composing a SQL Query

[geraldew]

I have to admit, I'm puzzled by your double layer query - shown after "The following query times out"

My impression is that you could just do the following (and as it's not my familiar dialect, I had to check that PostgreSQL supports both CURRENT_DATE and DISTINCT ).

SELECT 
    user_id,
    count( DISTINCT created_at) AS created_at_ctd
FROM 
    page_views
WHERE 
    created_at > CURRENT_DATE - 7
    AND 
    user_id IS NOT NULL
GROUP BY 
    user_id
HAVING 
    created_at_ctd >= 4
;

As you have limited the date span to the last 7 days, there is no particular need to derive what days of the week those are, as each date will be a different one anyway.

Admittedly this begs the question of which data interpretation you'd want for a longer date span, say over two weeks - i.e. whether you would want "different days" or "different days of the week".

p.s. it's cute that you've quoted the Ruby classes but it means nothing to those of us who work in SQL. I've assumed that created_at is a date column for display simplicity, but if it's a timestamp then some additional wrapping will be necessary (probably just a CAST to DATE).

[Jeremy Friesen]

I also think I had an off by one error in my query that your fix updates. So a double thank you.

[Jeremy Friesen]

I tried the following and it appears to get me the answer:

SELECT 
    user_id,
    count( DISTINCT  extract(isodow from created_at)) AS created_at_ctd
FROM 
    page_views
WHERE 
    created_at > CURRENT_DATE - 7
    AND 
    user_id IS NOT NULL
GROUP BY 
    user_id
HAVING 
     count( DISTINCT  extract(isodow from created_at)) >= 4
;

So thank you for helping me further simplify this thing.

The created_at, as a Ruby on Rails idiom is a timestamp.

I included the Ruby as it's reasonably accessible from a description (though I could make it easier).