w3resource

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:

FieldTypeNullKeyDefaultExtra
trans_idint(11)YES
customer_idint(11)YES
login_datedateYES

Data:

trans_idcustomer_idlogin_date
10130022019-09-01
10130022019-08-01
10230032018-09-13
10230022018-07-24
10330012019-09-25
10230042017-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.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

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