StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I was going to add this as an edit to my other answer, but it really is a different way of doing it so should be a separate answer. </p> <p>I think I prefer the other answer that I have given but this one should work even if there are gaps in the data.</p> <p>To set the parameters for the query, change the values of <code>period_start_date</code> and <code>period_days</code> in the <code>query_params</code> part of the with clause.</p> <pre><code>with query_params as ( select date '2011-01-01' as period_start_date, 7 as period_days ), summary_data as ( select user_id, (data_entry_date - period_start_date)/period_days as period_number, sum(wine_glasses_drunk) as wine_glasses_drunk, sum(whisky_shots_drunk) as whisky_shots_drunk, sum(beer_bottle_drunk) as beer_bottle_drunk from foobar cross join query_params group by user_id, (data_entry_date - period_start_date)/period_days ) select user_id, period_number, period_start_date + period_number * period_days as period_start_date, sum(wine_glasses_drunk) as wine_glasses_drunk, sum(whisky_shots_drunk) as whisky_shots_drunk, sum(beer_bottle_drunk) as beer_bottle_drunk from ( -- this weeks data select user_id, period_number, wine_glasses_drunk, whisky_shots_drunk, beer_bottle_drunk from summary_data union all -- last weeks data select user_id, period_number + 1 as period_number, -wine_glasses_drunk as wine_glasses_drunk, -whisky_shots_drunk as whisky_shots_drunk, -beer_bottle_drunk as beer_bottle_drunk from summary_data ) a cross join query_params where period_number <= (select max(period_number) from summary_data) group by user_id, period_number, period_start_date + period_number * period_days order by 1, 2 </code></pre> <p>And again, a <a href="http://sqlfiddle.com/#!1/2aee7/54" rel="nofollow">SQL Fiddle</a> is available.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload