PostgreSQL Advanced Queries - SQL Typing CST Test

PostgreSQL Advanced Queries — SQL Code

Demonstrates advanced SQL features including JOINs, CTEs, window functions, and complex aggregations.

-- 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;

SQL Language Guide

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

Practice Other Languages