MySQL: Grouping by date ranges

Question

Say we have a table of Products with the columns id, title, status, expires_at, created_at.

I can get the values for single week by specifying all the parameters:

SELECT
    count(id)
FROM 
    products
WHERE
    status = 'APPROVED' AND
    expires_at >= '2018-09-10 09:00:00' AND
    created_at <= '2018-09-10 09:00:00' AND
    deleted_at IS NULL;

How could we run a query that groups the products by weeks of the year 1-52 -- WHERE each week shows Products that were not expired: created_at <= week-end AND expires_at <= week-end or active:

Active_Products | Week_Of_Year 
----------------|---------------
274             | 2018-01
----------------|---------------
1011            | 2018-02
----------------|---------------
180             | 2018-03
----------------|---------------
990             | 2018-04
----------------|---------------
765             | 2018-05

I have updated this question with an SQL fiddle: http://sqlfiddle.com/#!9/e4830b/4

Possible duplicate of [How to group by week in MySQL?](https://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql) — Blue, Sep 10 '18 at 07:46
Its not a duplicate if you take time to read the full specs of the question. — AndrewMcLagan, Sep 10 '18 at 12:32

score 1 · Accepted Answer · answered Sep 10 '18 at 07:50

Try below query by calculating week start date and end date

select week(expires_at),count(id) as active_products
from product
where expires_at>SUBDATE(expires_at, weekday(expires_at)) and expires_at<DATE(expires_at + INTERVAL (6 - WEEKDAY(expires_at)) DAY)
group by week(expires_at)

Gordon Linoff · Answer 2 · 2018-09-10T12:40:21.710

1

I believe the logic you want for the active products in a given week is more like *created_at* >= week-start AND expires_at < week-end.

If you have products created during all weeks, then you can use a correlated subquery:

select week_of_year,
       (select count(*)
        from products t2
        where yearweek(t2.created_at) >= w.week_of_year and
              yearweek(t2.expires_at) < w.week_of_year
       ) as num_actives
from (select distinct yearweek(created_at) as week_of_year
      from products
     ) w;

edited Sep 10 '18 at 12:40

answered Sep 10 '18 at 08:56

Gordon Linoff

1,242,037
58
646
786

Ah I think this is really really close. Although its hard to understand what all the different table names are? where does the original `Products` table fit into that query? – AndrewMcLagan Sep 10 '18 at 12:35
@AndrewMcLagan . . . `t` is shorthand that I use for a generic table name. Now that you've specified the table, I've updated the answer. – Gordon Linoff Sep 10 '18 at 12:40
Not able to get this query working. I have created an SQLFiddle http://sqlfiddle.com/#!9/e4830b/4 – AndrewMcLagan Sep 10 '18 at 13:59

score 0 · Answer 3 · answered Sep 10 '18 at 07:51

You can use the function YEARWEEK(date) (https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek) to show the week number in a reada readable way including the year (yyyymm).

SELECT 
count(id) as active_products, YEARWEEK(expires_at)
FROM products
GROUP BY YEARWEEK(expires_at)

Using WEEK() will not make difference between years in the same week.

MySQL: Grouping by date ranges

3 Answers3