0

Customer table with purchases in a date range

Having some challenge doing this.

  1. Select all customers that made more than 2 purchases in an hour starting from a given datetime eg.2016-01-01.13:00:00

  2. Select all customers that made more than 2 purchases in a a day(24 hours) starting from a given datetime eg. 2016-01-01.15:30:05

    Select count(name) from cust_table where count(name) 
IN (BETWEEN (2016-01-01.13:00:00, 2016-01-01.14:00:00))
adu
  • 17
  • 3

1 Answers1

0

You need to get the identifiers (usernames or ID or so) for all users who have made more than two purchases. Something like the following should work:

SELECT name FROM purchases GROUP BY name HAVING COUNT(name) > 2

To get such rows where the purchases were made between some dates, just add a WHERE clause, so it becomes something like this

SELECT name FROM purchases WHERE pdate BETWEEN (2016-01-01.13:00:00) AND (2016-01-01.14:00:00) GROUP BY name HAVING COUNT(name) > 2

What this does is group the table rows by name with purchases between the specified dates. It then filters the groups using the HAVING COUNT(name) > 2 clause to retrieve only rows that appear more than twice.

TheNavigat
  • 864
  • 10
  • 30
  • thanks. datetime value gives sql error near '13:00:00. But when I do only date it works. meaning we cannot find between datetimes? – adu Feb 26 '18 at 14:17
  • Check this answer for the correct format of datetime: https://stackoverflow.com/a/5786722/1256113 – TheNavigat Feb 28 '18 at 22:26