CODEWITHSUNDEEP
mysqlsql
Alfred Beit
Alfred Beit

Reputation: 77

Writing SQL with timestamps

The data

CREATE TABLE IF NOT EXISTS `transactions` (
  `transactions_ts` timestamp ,
  `user_id` int(6) unsigned NOT NULL,
  `transaction_id` bigint, 
  `item` varchar(200), PRIMARY KEY(`transaction_id`)
) DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`transactions_ts`, `user_id`, `transaction_id`,`item` ) VALUES
  ('2016-06-18 13:46:51.0', 13811335,1322361417, 'glove'),
  ('2016-06-18 17:29:25.0', 13811335,3729362318, 'hat'), 
  ('2016-06-18 23::07:12.0', 13811335,1322363995,'vase' ),
  ('2016-06-19 07:14:56.0',13811335,7482365143, 'cup'),
  ('2016-06-19 21:59:40.0',13811335,1322369619,'mirror' ),
  ('2016-06-17 12:39:46.0',3378024101,9322351612, 'dress'),
  ('2016-06-17 20:22:17.0',3378024101,9322353031,'vase' ),
  ('2016-06-20 11:29:02.0',3378024101,6928364072,'tie'),
  ('2016-06-20 18:59:48.0',13811335,1322375547, 'mirror');

The question: for each user, show the first item that they ordered (first by time). I assume time as a whole timestamp (not time and date separately). My attempt

select 
min(transactions_ts) as first_trans, 
user_id, item
from transactions
group by user_id
order by first_trans;

I am sorry that may be it is a simple question, but one person tells me that my query is entirely wrong. And I have got no other means to test this claim of his

demo fiddle

Upvotes: 2

Views: 145

Answers (3)

nacho
nacho

Reputation: 5397

You can do it using a subquery to get the first transaction_ts for each user:

select user_id, item, transactions_ts
from transactions a
where transactions_ts=(select min(transactions_ts)
                       from transactions b
                       where b.user_id=a.user_id)

So your get:

  1. In the inner query get the first transaction time for each user
  2. In the outer query you get the row that has the time you got at point 1

Upvotes: 1

GMB
GMB

Reputation: 222462

If you are using MySQL 8.0, window function ROW_NUMBER() can be used to adress your use case, as follows:

SELECT  transactions_ts, user_id, item
FROM (
    SELECT 
        transactions_ts, 
        user_id, 
        item, 
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY transactions_ts) rn
    FROM transactions
) x WHERE rn = 1

The inner query ranks each record by ascending timestamp, within groups of records having the same user_id. The outer query filters in the first transaction of each customer.

Demo on DB Fiddle:

transactions_ts     |    user_id | item 
:------------------ | ---------: | :----
2016-06-18 13:46:51 |   13811335 | glove
2016-06-17 12:39:46 | 3378024101 | dress

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

This is a little bit more complicated than you thought.

To start with: "for each user" would translate to GROUP BY user_id, not to GROUP BY user_id, item.

But with GROUP BY user_id, you'd need an aggregation function saying "the item for the minimum transactions_ts". MySQL doesn't feature such an aggregation function.

The obvious solution is to make this two steps:

  1. Find the first transaction per user
  2. Show the items for these transactions

The query:

select *
from transactions
where (user_id, transactions_ts) in
(
  select user_id, min(transactions_ts)
  from transactions
  group by user_id
);

Another way to word the task is: "Give me the transactions for which no older transaction for the same user exists".

The query:

select *
from transactions t
where not exists
(
  select *
  from transactions t2
  where t2.user_id = t.user_id
  and t2.transactions_ts < t.transactions_ts
);

Upvotes: 1

Related Questions