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.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8