MySQL: Growth in Column Value for Given Foreign Key and Specific Date Range -

i have table in database update every day amount of pins on pinterest given url (page_id). how write query return page_id largest growth given date range?

typically, plan on setting @startdate = yesterday , setting @enddate = today, i'd flexibility adjust dates within query.

here current schema of pins table:

mysql> describe pins; +---------+---------+------+-----+---------+----------------+ | field   | type    | null | key | default |          | +---------+---------+------+-----+---------+----------------+ | id      | int(11) | no   | pri | null    | auto_increment | | page_id | int(11) | no   | mul | null    |                | | date    | date    | yes  |     | null    |                | | pins    | int(11) | yes  |     | null    |                | +---------+---------+------+-----+---------+----------------+ 

here desired output of query need with:

mysql> [fancy-mysql-query] order pins_dayoverday desc limit 1;  +-------------------+---------------------------+ | page_id           | pins_dayoverday           | +-------------------+---------------------------+ |             12345 |                     12345 | +-------------------+---------------------------+ 

any appreciated!

if pins column daily delta, net change in number of pins added/removed on given date, then:

select @startdate = date_format(now(),'%y-%m-%d') + interval -1 day select @enddate   = date_format(now(),'%y-%m-%d')  select t.page_id      , sum(t.pins) `pins_dayoverday`   mytable t  t.date >= @startdate    , t.date <= @enddate  group     t.page_id  order `pins_dayoverday` desc  limit 1  

if pins column cumulative , contains total count of number of effective pins (pins done on previous days plus pins added/removed today), assuming (page_id,date) unique:

select t.page_id      , (t.pins - s.pins) `pins_dayoverday`   mytable t   join mytable s     on s.page_id = t.page_id  t.date = @enddate    , s.date = @startdate  group     t.page_id  order `pins_dayoverday` desc  limit 1 

the query above assumes (page_id,date) unique. if same page_id can have multiple rows same date, we'd need add aggregate functions. (i provide query if need it.)