E-Commerce-Data-Analysis-SQL-Project-for-Customer-Behavior-Revenue-Optimization/02_sample_data.sql at main · coder77ai/E-Commerce-Data-Analysis-SQL-Project-for-Customer-Behavior-Revenue-Optimization · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
-- Sample Data for Customer & Order Analysis
-- This file populates the database with sample data

-- Insert Customers
INSERT INTO customers (customer_id, first_name, last_name, email, registration_date, city, country) VALUES
(1, 'John', 'Smith', 'john.smith@email.com', '2023-01-15', 'New York', 'USA'),
(2, 'Emily', 'Johnson', 'emily.johnson@email.com', '2023-02-20', 'Los Angeles', 'USA'),
(3, 'Michael', 'Williams', 'michael.williams@email.com', '2023-01-10', 'Chicago', 'USA'),
(4, 'Sarah', 'Brown', 'sarah.brown@email.com', '2023-03-05', 'Houston', 'USA'),
(5, 'David', 'Jones', 'david.jones@email.com', '2023-02-28', 'Phoenix', 'USA'),
(6, 'Jessica', 'Garcia', 'jessica.garcia@email.com', '2023-04-12', 'Philadelphia', 'USA'),
(7, 'Robert', 'Miller', 'robert.miller@email.com', '2023-01-25', 'San Antonio', 'USA'),
(8, 'Amanda', 'Davis', 'amanda.davis@email.com', '2023-05-18', 'San Diego', 'USA'),
(9, 'James', 'Rodriguez', 'james.rodriguez@email.com', '2023-03-22', 'Dallas', 'USA'),
(10, 'Lisa', 'Martinez', 'lisa.martinez@email.com', '2023-06-01', 'San Jose', 'USA'),
(11, 'William', 'Hernandez', 'william.hernandez@email.com', '2023-02-14', 'Austin', 'USA'),
(12, 'Ashley', 'Lopez', 'ashley.lopez@email.com', '2023-04-30', 'Jacksonville', 'USA');

-- Insert Products
INSERT INTO products (product_id, product_name, category, price, cost) VALUES
(101, 'Laptop Pro 15', 'Electronics', 1299.99, 800.00),
(102, 'Wireless Mouse', 'Electronics', 29.99, 10.00),
(103, 'Mechanical Keyboard', 'Electronics', 149.99, 60.00),
(104, 'USB-C Cable', 'Electronics', 19.99, 5.00),
(105, 'Office Chair', 'Furniture', 299.99, 150.00),
(106, 'Desk Lamp', 'Furniture', 49.99, 20.00),
(107, 'Standing Desk', 'Furniture', 599.99, 350.00),
(108, 'Coffee Maker', 'Appliances', 89.99, 40.00),
(109, 'Blender', 'Appliances', 79.99, 35.00),
(110, 'Headphones', 'Electronics', 199.99, 80.00);

-- Insert Orders (spread across multiple months)
INSERT INTO orders (order_id, customer_id, order_date, status) VALUES
(1001, 1, '2023-02-10', 'completed'),
(1002, 2, '2023-03-15', 'completed'),
(1003, 1, '2023-03-20', 'completed'),
(1004, 3, '2023-02-25', 'completed'),
(1005, 4, '2023-04-10', 'completed'),
(1006, 2, '2023-04-18', 'completed'),
(1007, 5, '2023-03-30', 'completed'),
(1008, 1, '2023-05-05', 'completed'),
(1009, 6, '2023-05-20', 'completed'),
(1010, 3, '2023-04-28', 'completed'),
(1011, 7, '2023-02-15', 'completed'),
(1012, 2, '2023-05-25', 'completed'),
(1013, 8, '2023-06-10', 'completed'),
(1014, 1, '2023-06-15', 'completed'),
(1015, 9, '2023-04-05', 'completed'),
(1016, 4, '2023-05-12', 'completed'),
(1017, 10, '2023-06-20', 'completed'),
(1018, 5, '2023-05-30', 'completed'),
(1019, 11, '2023-03-10', 'completed'),
(1020, 2, '2023-06-28', 'completed'),
(1021, 12, '2023-05-15', 'completed'),
(1022, 6, '2023-06-25', 'completed'),
(1023, 1, '2023-07-05', 'completed'),
(1024, 3, '2023-05-20', 'completed'),
(1025, 7, '2023-06-12', 'completed');

-- Insert Order Items
INSERT INTO order_items (order_item_id, order_id, product_id, quantity, unit_price) VALUES
(1, 1001, 101, 1, 1299.99),
(2, 1001, 102, 2, 29.99),
(3, 1002, 103, 1, 149.99),
(4, 1002, 104, 3, 19.99),
(5, 1003, 110, 1, 199.99),
(6, 1003, 102, 1, 29.99),
(7, 1004, 105, 1, 299.99),
(8, 1005, 101, 1, 1299.99),
(9, 1005, 103, 1, 149.99),
(10, 1006, 107, 1, 599.99),
(11, 1006, 106, 2, 49.99),
(12, 1007, 108, 1, 89.99),
(13, 1008, 101, 1, 1299.99),
(14, 1008, 110, 1, 199.99),
(15, 1009, 103, 2, 149.99),
(16, 1010, 105, 1, 299.99),
(17, 1010, 106, 1, 49.99),
(18, 1011, 109, 1, 79.99),
(19, 1012, 101, 1, 1299.99),
(20, 1012, 102, 1, 29.99),
(21, 1012, 104, 2, 19.99),
(22, 1013, 110, 1, 199.99),
(23, 1014, 107, 1, 599.99),
(24, 1015, 103, 1, 149.99),
(25, 1015, 102, 2, 29.99),
(26, 1016, 108, 1, 89.99),
(27, 1016, 109, 1, 79.99),
(28, 1017, 101, 1, 1299.99),
(29, 1018, 105, 1, 299.99),
(30, 1019, 110, 1, 199.99),
(31, 1020, 101, 1, 1299.99),
(32, 1020, 110, 1, 199.99),
(33, 1021, 107, 1, 599.99),
(34, 1022, 103, 1, 149.99),
(35, 1023, 101, 1, 1299.99),
(36, 1023, 103, 1, 149.99),
(37, 1023, 110, 1, 199.99),
(38, 1024, 105, 1, 299.99),
(39, 1025, 108, 1, 89.99);