sql-practice/schema.sql at main · pengboyu-dev/sql-practice · 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
-- ============================================================
-- 模拟业务数据库结构
-- 场景：网约车 / 电商平台均适用的通用用户运营场景
-- ============================================================

-- 用户表
CREATE TABLE users (
    user_id       VARCHAR(20) PRIMARY KEY,
    register_date DATE        NOT NULL,
    city          VARCHAR(20),
    user_type     VARCHAR(10) -- 'new' | 'returning'
);

-- 订单表
CREATE TABLE orders (
    order_id     VARCHAR(20) PRIMARY KEY,
    user_id      VARCHAR(20) NOT NULL REFERENCES users(user_id),
    order_date   DATE        NOT NULL,
    amount       NUMERIC(10, 2),
    city         VARCHAR(20),
    used_coupon  BOOLEAN     DEFAULT FALSE,
    coupon_value NUMERIC(10, 2) DEFAULT 0
);

-- 用户行为事件表（用于漏斗分析）
CREATE TABLE events (
    event_id    SERIAL PRIMARY KEY,
    user_id     VARCHAR(20) NOT NULL,
    event_type  VARCHAR(50) NOT NULL,
    -- 事件类型示例：
    -- 'push_received'   收到推送
    -- 'push_clicked'    点击推送
    -- 'page_viewed'     进入活动页
    -- 'coupon_claimed'  领取优惠券
    -- 'order_placed'    完成下单
    event_time  TIMESTAMP   NOT NULL
);

-- ============================================================
-- 造假数据（供本地测试用）
-- ============================================================

INSERT INTO users VALUES
('U001', '2025-01-15', '杭州', 'new'),
('U002', '2024-06-20', '上海', 'returning'),
('U003', '2025-02-28', '杭州', 'new'),
('U004', '2024-03-10', '南京', 'returning'),
('U005', '2025-03-01', '上海', 'new'),
('U006', '2024-11-05', '杭州', 'returning'),
('U007', '2025-03-10', '南京', 'new');

INSERT INTO orders VALUES
('O1001', 'U001', '2025-03-01', 28.50, '杭州', TRUE,  5.00),
('O1002', 'U002', '2025-03-01', 15.00, '上海', FALSE, 0.00),
('O1003', 'U001', '2025-03-03', 32.00, '杭州', FALSE, 0.00),
('O1004', 'U003', '2025-03-05', 22.00, '杭州', TRUE,  8.00),
('O1005', 'U002', '2025-03-10', 18.50, '上海', TRUE,  5.00),
('O1006', 'U004', '2025-03-10', 45.00, '南京', FALSE, 0.00),
('O1007', 'U001', '2025-03-15', 19.00, '杭州', TRUE,  5.00),
('O1008', 'U006', '2025-03-15', 33.00, '杭州', FALSE, 0.00),
('O1009', 'U002', '2025-03-20', 27.00, '上海', FALSE, 0.00),
('O1010', 'U004', '2025-03-20', 38.00, '南京', TRUE,  10.00);

INSERT INTO events (user_id, event_type, event_time) VALUES
('U001', 'push_received',  '2025-03-10 09:00:00'),
('U001', 'push_clicked',   '2025-03-10 09:01:00'),
('U001', 'page_viewed',    '2025-03-10 09:02:00'),
('U001', 'coupon_claimed', '2025-03-10 09:03:00'),
('U001', 'order_placed',   '2025-03-10 09:15:00'),
('U002', 'push_received',  '2025-03-10 09:00:00'),
('U002', 'push_clicked',   '2025-03-10 09:02:00'),
('U002', 'page_viewed',    '2025-03-10 09:04:00'),
('U003', 'push_received',  '2025-03-10 09:00:00'),
('U003', 'push_clicked',   '2025-03-10 09:05:00'),
('U004', 'push_received',  '2025-03-10 09:00:00'),
('U005', 'push_received',  '2025-03-10 09:00:00'),
('U005', 'push_clicked',   '2025-03-10 09:03:00'),
('U005', 'page_viewed',    '2025-03-10 09:06:00'),
('U005', 'coupon_claimed', '2025-03-10 09:08:00');