Скачать или смотреть Leetcode MEDIUM 3657 - GROUP BY & Common Table Expressions in SQL - Find Loyal Customers | EDS

Question: https://leetcode.com/problems/find-lo...

SQL Schema:
CREATE TABLE if not exists customer_transactions (
transaction_id INT,
customer_id INT,
transaction_date DATE,
amount DECIMAL(10,2),
transaction_type VARCHAR(20)
)
Truncate table customer_transactions
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('1', '101', '2024-01-05', '150.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('2', '101', '2024-01-15', '200.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('3', '101', '2024-02-10', '180.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('4', '101', '2024-02-20', '250.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('5', '102', '2024-01-10', '100.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('6', '102', '2024-01-12', '120.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('7', '102', '2024-01-15', '80.0', 'refund')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('8', '102', '2024-01-18', '90.0', 'refund')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('9', '102', '2024-02-15', '130.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('10', '103', '2024-01-01', '500.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('11', '103', '2024-01-02', '450.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('12', '103', '2024-01-03', '400.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('13', '104', '2024-01-01', '200.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('14', '104', '2024-02-01', '250.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('15', '104', '2024-02-15', '300.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('16', '104', '2024-03-01', '350.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('17', '104', '2024-03-10', '280.0', 'purchase')
insert into customer_transactions (transaction_id, customer_id, transaction_date, amount, transaction_type) values ('18', '104', '2024-03-15', '100.0', 'refund')

Pandas Schema:
data = [[1, 101, '2024-01-05', 150.0, 'purchase'], [2, 101, '2024-01-15', 200.0, 'purchase'], [3, 101, '2024-02-10', 180.0, 'purchase'], [4, 101, '2024-02-20', 250.0, 'purchase'], [5, 102, '2024-01-10', 100.0, 'purchase'], [6, 102, '2024-01-12', 120.0, 'purchase'], [7, 102, '2024-01-15', 80.0, 'refund'], [8, 102, '2024-01-18', 90.0, 'refund'], [9, 102, '2024-02-15', 130.0, 'purchase'], [10, 103, '2024-01-01', 500.0, 'purchase'], [11, 103, '2024-01-02', 450.0, 'purchase'], [12, 103, '2024-01-03', 400.0, 'purchase'], [13, 104, '2024-01-01', 200.0, 'purchase'], [14, 104, '2024-02-01', 250.0, 'purchase'], [15, 104, '2024-02-15', 300.0, 'purchase'], [16, 104, '2024-03-01', 350.0, 'purchase'], [17, 104, '2024-03-10', 280.0, 'purchase'], [18, 104, '2024-03-15', 100.0, 'refund']]
customer_transactions = pd.DataFrame({
"transaction_id": pd.Series(dtype="int"),
"customer_id": pd.Series(dtype="int"),
"transaction_date": pd.Series(dtype="datetime64[ns]"),
"amount": pd.Series(dtype="float"),
"transaction_type": pd.Series(dtype="string")
})

#leetcode #datascience #tutorial