Mysql: Select number of transactions from 1 week ago

I have a table like this:

transactions
+------------+------------+ 
| date_id    | t_count    | 
+------------+------------+ 
| 2019-01-30 |       100  | 
| 2019-01-29 |        99  | 
| 2019-01-28 |        98  | 
| 2019-01-27 |        97  | 
| 2019-01-26 |        96  | 
| 2019-01-25 |        95  | 
| 2019-01-24 |        94  | 
| 2019-01-23 |        93  | 
| 2019-01-22 |        92  | 
| 2019-01-21 |        91  | 
| 2019-01-20 |        90  | 
+------------+------------+

I would like to get t_count for the date as well as t_count for one week prior, like so:

+------------+------------+------------------+ 
| date_id     | t_count   | t_count_7d_prev  | 
+------------+------------+------------------+ 
| 2019-01-30 |       100  |              93  | 
| 2019-01-29 |        99  |              92  | 
| 2019-01-28 |        98  |              91  | 
| 2019-01-27 |        97  |              90  | 
+------------+------------+------------------+

I’ve tried the following query but it gives me nulls for the last column.

select
  date_id,
  t_count,
  (select t_count 
   from transactions 
   where date(date_id) = date(date_id) - interval 7 day) as t_count_7d_prev
from 
  transactions

Is there another way that I should try subtracting the dates?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You can use window functions. If date_id is a date:

select date_id, t_count,
       sum(t_count) over (order by date_id
                          range between interval 7 day preceding and interval 7 day preceding
                         ) as t_count_7d_prev
from transactions t;

Or, if you are sure you have data every date, then use lag():

select t.*,
       lag(t_count, 7) over (order by date_id) as t_count_7d_prev
from t;

Method 2

This is a simple internal join.

select a.date_id, a.t_count, b.t_count as t_count_7d_prev
from
    transactions a left join transaction b
    on a.dat_id = DATE_ADD(b.date_id,INTERVAL 7 DAY)

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0