Mode:
Duration:
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
-- Create tables with relationships
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_login TIMESTAMP
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(200) NOT NULL,
content TEXT,
status VARCHAR(20) DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Insert sample data
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com'),
('charlie', 'charlie@example.com');
INSERT INTO posts (user_id, title, content, status) VALUES
(1, 'First Post', 'This is my first blog post.', 'published'),
(1, 'Second Post', 'Another interesting post.', 'published'),
(2, 'Draft Post', 'This is still a draft.', 'draft');
INSERT INTO comments (post_id, user_id, content) VALUES
(1, 2, 'Great first post!'),
(1, 3, 'Thanks for sharing.'),
(2, 3, 'Very informative.');
-- Complex queries with JOINs, aggregations, and CTEs
WITH user_stats AS (
SELECT
u.id,
u.username,
COUNT(DISTINCT p.id) as post_count,
COUNT(DISTINCT c.id) as comment_count,
MAX(p.created_at) as last_post_date
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
LEFT JOIN comments c ON u.id = c.user_id
GROUP BY u.id, u.username
),
popular_posts AS (
SELECT
p.id,
p.title,
u.username as author,
COUNT(c.id) as comment_count,
RANK() OVER (ORDER BY COUNT(c.id) DESC) as popularity_rank
FROM posts p
JOIN users u ON p.user_id = u.id
LEFT JOIN comments c ON p.id = c.post_id
WHERE p.status = 'published'
GROUP BY p.id, p.title, u.username
)
SELECT
us.username,
us.post_count,
us.comment_count,
us.last_post_date,
COALESCE(pp.title, 'No posts') as most_popular_post,
COALESCE(pp.comment_count, 0) as most_popular_post_comments
FROM user_stats us
LEFT JOIN popular_posts pp ON pp.popularity_rank = 1
ORDER BY us.post_count DESC, us.comment_count DESC;
-- Window functions example
SELECT
u.username,
p.title,
p.created_at,
LAG(p.created_at) OVER (
PARTITION BY u.id
ORDER BY p.created_at
) as previous_post_date,
COUNT(*) OVER (
PARTITION BY u.id
) as total_user_posts,
ROW_NUMBER() OVER (
PARTITION BY u.id
ORDER BY p.created_at
) as post_sequence
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY u.username, p.created_at;Coding works best on desktop or with an external keyboard.