How to do frequency SQL queries per hour and per day?

Question

How to do frequency SQL queries per hour and per day?

Navigation

#1 by (2 votes)

2

I want to know when my application is the most used per hour and per day for comparative purposes for marketing. I can do it with the PostgreSQL database but the problem is that I do not know how to segment the results by hour and day.

I have two tables: swipe and eclipse_hastag :

Here is the table eclipse_hastag :

Here is the swipe table:

How to know how many times we have swipe.state=3, 6 o 9 per hour per week ordered in decreasing order?

sql

asked by ThePassenger 17.05.2017 в 10:58

source

1 answer

Alternative to replace the SideMenu of JFoenix How to collect all the image tags from an html page on RUBY

score 2 · Answer 1

As far as I understand, only the second table is necessary. This consultation would count the subscriber_id per day of the week (Monday, Tuesday ... etc)

select count(subscriber_id) as total, to_char(date, 'W') as week_day from swipe
where state in (3,6,9)
group by to_char(date, 'W') 
order by count(subscriber_id) desc

To group and count for another time / date, substitute the 'W' representing the day of the week for any other value in this list: link