Group-Buys-SQL-Database/queries.sql at main · ericjoye/Group-Buys-SQL-Database · 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
-- Example Queries for Group Buys Database

-- ========================================
-- BASIC SELECT QUERIES
-- ========================================

-- Get all active group buys
SELECT * FROM group_buys
WHERE status = 'active';

-- List all products sorted by price
SELECT product_name, regular_price
FROM products
ORDER BY regular_price DESC;


-- ========================================
-- JOIN QUERIES
-- ========================================

-- Show group buys with product details
SELECT
    gb.group_buy_id,
    p.product_name,
    p.regular_price,
    gb.group_price,
    gb.status
FROM group_buys gb
JOIN products p ON gb.product_id = p.product_id;

-- List all participants with their usernames and group buy info
SELECT
    u.username,
    p.product_name,
    gb.group_price,
    pt.joined_date
FROM participants pt
JOIN users u ON pt.user_id = u.user_id
JOIN group_buys gb ON pt.group_buy_id = gb.group_buy_id
JOIN products p ON gb.product_id = p.product_id;


-- ========================================
-- AGGREGATE FUNCTIONS
-- ========================================

-- Count participants in each group buy
SELECT
    gb.group_buy_id,
    p.product_name,
    COUNT(pt.participant_id) as participant_count
FROM group_buys gb
JOIN products p ON gb.product_id = p.product_id
LEFT JOIN participants pt ON gb.group_buy_id = pt.group_buy_id
GROUP BY gb.group_buy_id, p.product_name;

-- Calculate total savings per group buy
SELECT
    p.product_name,
    (p.regular_price - gb.group_price) as savings_per_person,
    COUNT(pt.participant_id) as participants,
    (p.regular_price - gb.group_price) * COUNT(pt.participant_id) as total_savings
FROM group_buys gb
JOIN products p ON gb.product_id = p.product_id
LEFT JOIN participants pt ON gb.group_buy_id = pt.group_buy_id
GROUP BY gb.group_buy_id, p.product_name, p.regular_price, gb.group_price;


-- ========================================
-- WHERE CLAUSE EXAMPLES
-- ========================================

-- Find group buys that met minimum participation
SELECT
    gb.group_buy_id,
    p.product_name,
    gb.minimum_participants,
    COUNT(pt.participant_id) as actual_participants
FROM group_buys gb
JOIN products p ON gb.product_id = p.product_id
LEFT JOIN participants pt ON gb.group_buy_id = pt.group_buy_id
GROUP BY gb.group_buy_id, p.product_name, gb.minimum_participants
HAVING COUNT(pt.participant_id) >= gb.minimum_participants;

-- Get electronics category group buys
SELECT
    gb.group_buy_id,
    p.product_name,
    p.category,
    gb.group_price
FROM group_buys gb
JOIN products p ON gb.product_id = p.product_id
WHERE p.category = 'Electronics'
AND gb.status = 'active';