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.
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
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework