SQL Challenges-1: Find the first login date for each customer
SQL Challenges-1: Exercise-25 with Solution
From the following table, write a SQL query to find the first login date for each customer. Return customer id, login date.
Input:
Table: bank_trans
Structure:
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
trans_id | int(11) | YES | |||
customer_id | int(11) | YES | |||
login_date | date | YES |
Data:
trans_id | customer_id | login_date |
---|---|---|
101 | 3002 | 2019-09-01 |
101 | 3002 | 2019-08-01 |
102 | 3003 | 2018-09-13 |
102 | 3002 | 2018-07-24 |
103 | 3001 | 2019-09-25 |
102 | 3004 | 2017-09-05 |
Sample Solution:
SQL Code(MySQL):
DROP TABLE IF EXISTS bank_trans;
CREATE TABLE bank_trans(trans_id int, customer_id int, login_date date);
INSERT INTO bank_trans VALUES (101, 3002, '2019-09-01');
INSERT INTO bank_trans VALUES (101, 3002, '2019-08-01');
INSERT INTO bank_trans VALUES (102, 3003, '2018-09-13');
INSERT INTO bank_trans VALUES (102, 3002, '2018-07-24');
INSERT INTO bank_trans VALUES (103, 3001, '2019-09-25');
INSERT INTO bank_trans VALUES (102, 3004, '2017-09-05');
SELECT * FROM bank_trans;
SELECT customer_id, MIN(login_date) first_login
FROM bank_trans
GROUP BY customer_id;
Sample Output:
customer_id|first_login| -----------|-----------| 3001| 2019-09-25| 3002| 2018-07-24| 3003| 2018-09-13| 3004| 2017-09-05|
SQL Code Editor:
Contribute your code and comments through Disqus.
Previous: Internal changes of beds.
Next: Find those salespersons whose commission is less than ten thousand.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/sql-exercises/challenges-1/sql-challenges-1-exercise-25.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics