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
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- 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