📊 Day 21 Data Download

Amazon Subscription Churn & Renewal Analysis Dataset

Multi-Table JOINs Churn Analysis Revenue Tracking Risk Segmentation

Table 1: subscriptions (30 rows)

subscription_id user_id plan_id start_date end_date status
SUB001 U1001p012023-01-152024-01-15active
sub002 u1002 P0215/06/2023NULLCANCELLED
SUB003U1003p012023-03-202024-03-20Active
Sub004u1004 P03 2023/07/1010-01-2024expired
SUB005U1001p0220-02-20242025-02-20ACTIVE
sub006 U1005 P042023-04-12NULLcancelled
SUB007u1006p0115/07/20232024-07-15Active
Sub008U1007P032023/08/2222-02-2024EXPIRED
SUB009u1008 p02 2023-09-102024-09-10active
sub010U1009P0105-10-2023NULLCANCELLED
SUB011 u1010p042023-11-182024-11-18Active
SUB012U1003p0225/03/20242025-03-25ACTIVE
sub013 u1011 P012023/12/0505-12-2024expired
Sub014U1012p032024-01-08NULLActive
SUB015u1013 P04 15-01-20242025-01-15ACTIVE
sub016U1014p012024-02-20NULLcancelled
SUB017U1015P0201/03/20242025-03-01Active
Sub018 u1016 p032024/03/1515-09-2024EXPIRED
SUB019U1017P012024-04-10NULLactive
sub020u1018 p04 25-04-20242025-04-25ACTIVE
SUB021U1019P022024-05-12NULLCANCELLED
Sub022 U1020 p0118/05/20242025-05-18Active
SUB023u1006P032024/06/20NULLactive
sub024U1021p022024-07-052025-07-05ACTIVE
SUB025 u1022 P04 15-07-2024NULLcancelled
SUB026U1023p012024-08-10NULLActive
sub027U1024P0222/08/20242025-08-22ACTIVE
Sub028 u1025 p032024/09/1212-03-2025expired
SUB029U1010P012024-10-05NULLactive
sub030u1026p0418-10-20242025-10-18ACTIVE

Table 2: users (26 rows)

user_id user_name email country registration_date
U1001alice johnsonalice.j@email.comUSA2022-11-10
u1002 BOB SMITHbob.s@email.comuk15/05/2023
U1003 Carlos Martinez carlos.m@email.comSPAIN2023-01-08
u1004DIANA CHENdiana.c@email.comchina2023/06/22
U1005erik muellererik.m@email.comGermany10-03-2023
U1006 Fatima Al-Saidfatima.a@email.comUAE2023-04-05
u1007GEORGE PATELgeorge.p@email.comindia18/07/2023
U1008hannah kimhannah.k@email.comSOUTH KOREA2023/08/30
U1009 Ivan Petrov ivan.p@email.comRussia2023-09-12
u1010Julia Santosjulia.s@email.combrazil15-10-2023
U1011KEVIN BROWNkevin.b@email.comCanada2023-11-20
u1012laura garcialaura.g@email.comMEXICO05/01/2024
U1013 Mohammed Alimohammed.a@email.comegypt2024/01/18
U1014NINA WANGnina.w@email.comChina2024-02-10
u1015oliver schmidtoliver.s@email.comGERMANY25-02-2024
U1016 Paula Silva paula.s@email.comPortugal2024-03-08
U1017quinn taylorquinn.t@email.comaustralia05/04/2024
u1018RAHUL SHARMArahul.s@email.comIndia2024/04/22
U1019sophia rossisophia.r@email.comITALY2024-05-05
u1020 THOMAS JONESthomas.j@email.comuk12-05-2024
U1021Uma Krishnanuma.k@email.comIndia2024-06-28
u1022victor lopezvictor.l@email.comSPAIN10/07/2024
U1023 WENDY CHEN wendy.c@email.comSingapore2024/08/05
U1024Xavier Duboisxavier.d@email.comfrance2024-08-18
u1025YUKI TANAKAyuki.t@email.comJapan08-09-2024
U1026zara ahmedzara.a@email.comPAKISTAN2024-10-15

Table 3: plans (4 rows)

plan_id plan_name price_monthly billing_cycle
p01Prime Monthly14.99monthly
P02 PRIME ANNUAL139.00yearly
p03Student Prime7.49MONTHLY
P04family plan19.99Monthly

Table 4: payment_history (30 rows)

payment_id subscription_id payment_date amount payment_status
PAY001 SUB001 2023-01-1514.99success
pay002sub00215/06/2023139.00FAILED
PAY003SUB0032023-03-2014.99Success
Pay004 Sub004 2023/07/107.49success
PAY005SUB00115-02-2023NULLpending
pay006SUB0052024-02-20139.00SUCCESS
PAY007 sub006 12/04/202319.99Failed
Pay008SUB0072023/07/1514.99success
PAY009Sub0082023-08-227.49SUCCESS
pay010SUB00910-09-2023139.00success
PAY011 sub0102023-10-0514.99FAILED
PAY012SUB01118/11/202319.99Success
pay013 SUB012 2024/03/25139.00success
Pay014sub0132023-12-05NULLpending
PAY015Sub01408-01-20247.49SUCCESS
pay016SUB0152024-01-1519.99success
PAY017sub01620/02/202414.99Failed
Pay018 SUB017 2024/03/01139.00success
PAY019Sub0182024-03-157.49SUCCESS
pay020SUB01910-04-2024NULLpending
PAY021 sub020 2024-04-2519.99success
Pay022SUB02112/05/2024139.00FAILED
PAY023Sub0222024/05/1814.99Success
pay024SUB0232024-06-207.49success
PAY025sub02405-07-2024139.00SUCCESS
pay026 SUB0252024-07-1519.99Failed
PAY027SUB02610/08/202414.99success
Pay028 sub027 2024/08/22139.00SUCCESS
PAY029Sub0282024-09-12NULLpending
pay030SUB02905-10-202414.99success

📖 How to Import Data

MySQL:

CREATE DATABASE amazon_subs; USE amazon_subs; CREATE TABLE subscriptions ( subscription_id VARCHAR(50), user_id VARCHAR(50), plan_id VARCHAR(50), start_date VARCHAR(50), end_date VARCHAR(50), status VARCHAR(50) ); LOAD DATA INFILE 'day_21_subscriptions.csv' INTO TABLE subscriptions FIELDS TERMINATED BY ',' IGNORE 1 ROWS;

PostgreSQL:

CREATE TABLE subscriptions ( subscription_id VARCHAR(50), user_id VARCHAR(50), plan_id VARCHAR(50), start_date VARCHAR(50), end_date VARCHAR(50), status VARCHAR(50) ); COPY subscriptions FROM '/path/to/day_21_subscriptions.csv' DELIMITER ',' CSV HEADER;

Note: All columns are VARCHAR to preserve messy data. Clean them in your queries!

🔗 Connect with me

← Back to Challenge LinkedIn GitHub

Rahul Kanche — exploring data, decoding clarity!