SQL how to count the number of visits?

Question

PostgreSQL

Hello!

I have purchase data:
customer ID (ID_Customer, time of purchase (date_transactions, store number (id_store), receipt ID (id_receipt).

Please tell me how to calculate the number of visits by the customer to the store?

He could buy several checks for one trip to shop.So a visit is a purchase within one hour in one

store.I guess you need to use date_trunc

Answer 1

with tt as (
select ID_Customer id, id_store,  date_trunc('hour', date_transactions) trun 
from transactions t
)
select id,  id_store, count(id)
from tt
group by 1,2