CODEWITHSUNDEEP
mysqlsqlpostgresql
jovicbg
jovicbg

Reputation: 1553

Group by ID and find min and max of time in SQL

I got my first task in SQL, but got confused by order function. Here is an example of the table:

customer_id  is_employee     purchase_datetime
  42525           0      2007-02-19 12:49:34:560000
  42525           0      2007-02-22 16:14:55:220000
  42525           0      2007-03-02 10:56:15:200000
  52525           1      2007-02-22 14:45:18:130000
  46233           0      2007-02-21 10:29:39:010000
  53364           0      2007-02-13 08:33:34:320000
  53364           0      2007-02-20 10:01:09:540000

I need to find unique users, that would be DISTINCT of customer_id, show if it's employee or not and find earliest and latest purchase for every customer. I'm totally beginner for SQL and have no idea where to start.

Upvotes: 0

Views: 47

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51609

probably:

select customer_id,  is_employee, max(purchase_datetime),min(purchase_datetime)
from table_name
group by customer_id,  is_employee
;

It was assumed that you cant be both employee and not one same time

Upvotes: 1

Tom
Tom

Reputation: 9878

  SELECT 
    customer_id
    ,is_employee
    ,MIN(purchase_datetime) AS 'EarliestPurchase'
    ,MAX(purchase_datetime) AS 'LatestPurchase' 
  FROM tbl 
  GROUP BY customer_id, is_employee

Upvotes: 1

Related Questions