﻿ SQL Challenges-1: Find the first login date for each customer - 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

Data:

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;

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:

﻿

## 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