Count Records Created Within the Last 7 Days

Count records created within the last 7 days

You can add a where-condition like this:

self.favorites.where('created_at >= ?', 1.week.ago).count

And for your calculate_user_score method, you probably want to do that for links as well:

def calculate_user_score
  unless new_record?
    self.score = (links.where('created_at >= ?', 1.week.ago).count * 5) +
      (favorites.where('created_at >= ?', 1.week.ago).count * 0.5)
  end
end  

MySQL - How to get record count for each day of last 7 days?

I was waiting for you to post your own effort, but since somebody has already "jumped the gun" and started to post answers, so:

Assuming the name of the table is my_table, then try:

select date(message_datetime) as message_date, count(*) as cnt from my_table
where datediff(curdate(), date(message_datetime)) < 7
group by date(message_datetime)
order by date(message_datetime) desc

Update

Following the Strawberry's suggestion, here is an updated query that should be more performant if message_datetime is an indexed column:

select date(message_datetime) as message_date, count(*) as cnt from my_table
where message_date_time >= date_sub(curdate(), interval 6 day)
group by date(message_datetime)
order by date(message_datetime) desc

Count users created in last 7 days in rails ? [need help in syntax of Count]

This count:

@count_users = User.count('comment')

Generates the following SQL:

SELECT COUNT(comment) FROM "users"

So unless you have a comment column in your users table, it is not counting the right objects.

If you have a Comment model, you can use the following to count all comments created in the past 7 days:

Comment.where('created_at >= ?', Time.zone.now - 7.days).count # count for a week
Comment.where('created_at >= ?', Time.zone.now - 1.months).count # count for a month

If you want to count the Comments of a specific User, you can use this (assuming a Comment belongs_to a User and a User has_many Comments):

Comment.where(user_id: user.i).where('created_at >= ?', Time.zone.now - 7.days).count

How to get last 7 days records with 0 counts

Looking directly Sql : How to include "zero" / "0" results in COUNT aggregate?

Into a same table : How to get the record if Count is zero in Laravel

You need to add an outer join into your request with Eloquent.

SQL Server: Get Count of past 7 days for each day

If 2012+ You can use the Window functions with the preceding clause

Declare @YourTable table (updateDate date,Type varchar(25))
Insert Into @YourTable values
('2016-05-31','Thing1'),
('2016-05-31','Thing2'),
('2016-05-31','Thing3'),
('2016-05-30','Thing1'),
('2016-05-29','Thing2'),
('2016-05-28','Thing1'), 
('2016-05-28','Thing3'), 
('2016-05-27','Thing1'),
('2016-05-26','Thing1')

Select *,ThingCount=sum(1) over(Partition By Type order by updateDate rows between 7 preceding and current row) 
 From  @YourTable

Returns

PostgreSQL combining a count, a last 7 days count and a last 30 days count Grouped by days

If your data may have gaps in days, then you need a range frame specification rather than a rows frame. Happily Postgres supports this specification, so you can do:

select
    datetimea::date date,
    count(*) "day",
    sum(count(*)) over(
        order by datetimea::date
        range between '7 day' preceding and current row
    ) l7,
        sum(count(*)) over(
        order by datetimea::date
        range between '30 day' preceding and current row
    ) l30
from mytable
group by datetimea::date
order by datetimea::date 

Demo on DB Fiddle:

date       | day | l7 | l30
:--------- | --: | -: | --:
2020-03-16 |   1 |  1 |   1
2020-03-17 |   1 |  2 |   2
2020-03-18 |   1 |  3 |   3
2020-03-19 |   1 |  4 |   4
2020-03-20 |   1 |  5 |   5
2020-03-21 |   1 |  6 |   6
2020-03-22 |   2 |  8 |   8
2020-03-28 |   1 |  4 |   9
2020-03-29 |   1 |  4 |  10
2020-03-30 |   2 |  4 |  12
2020-03-31 |   1 |  5 |  13

How can I count entries for each day of the last 7 days in mysql?

If you want something like the following result :

date       |  num_texts
-------------------------
2015-10-28 | 3
2015-10-27 | 1
2015-10-26 | 8
etc.

then look at the query at MySQL group by date and convert from unix timestamp

However, if you would like the result to be something like :

days_ago   |  num_texts
-------------------------
0          | 6
1          | 3
2          | 1
3          | 8
etc.

then you can use the following query :

SELECT * 
FROM (SELECT DATEDIFF(now(), FROM_UNIXTIME(start_date)) AS days_ago, COUNT(id) AS num_texts 
      FROM USER_TEXT 
      GROUP BY DATE(FROM_UNIXTIME(start_date))) AS temp 
WHERE days_ago <= 7

---

Related Topics