Premium
SQL Snippets.

Create a Simple Pivot Table (Rows to Columns)

Transform rows into columns using SQL for clearer data representation and reporting, enabling easy comparison of categorical data across different attributes.

Querying and Extracting Data from JSON/JSONB Columns (PostgreSQL)

Learn to query JSON or JSONB columns in PostgreSQL, extracting specific values and filtering records based on nested JSON data. Essential for modern web applications.

Basic Case-Insensitive Full-Text Search (PostgreSQL)

Implement simple case-insensitive full-text search in PostgreSQL using the `ILIKE` operator for flexible data querying across multiple columns.

Traverse Hierarchical Data with Recursive CTE (PostgreSQL)

Learn to query hierarchical data structures like categories or comment threads using `WITH RECURSIVE` CTEs in PostgreSQL to find ancestors or descendants.

Conditional Aggregation for Dynamic Summary Reports

Generate powerful summary reports with conditional aggregation using `CASE` statements inside aggregate functions. Count or sum specific categories within a single query.

Find and Delete Duplicate Records (Non-Window Function Method)

Discover how to identify and remove duplicate rows in a table based on specific columns without relying on window functions. Preserve one unique record.

SQL Query: Rank Items Within Groups Using Window Functions

Learn to rank items, such as products by sales within each category, using SQL window functions like ROW_NUMBER() for detailed reporting.

SQL Query: Structure Complex Logic with Common Table Expressions (CTEs)

Enhance SQL query readability and modularity by breaking down complex logic into manageable, reusable steps using Common Table Expressions (WITH clause).

SQL Query: Perform UPSERT Operations with INSERT ON CONFLICT

Efficiently insert new records or update existing ones in a single SQL statement using the `INSERT ... ON CONFLICT DO UPDATE` (UPSERT) clause for data synchronization.

SQL Query: Implement Data Pagination with OFFSET and LIMIT

Fetch specific pages of data efficiently from large datasets using SQL's `OFFSET` and `LIMIT` (or `FETCH NEXT`) clauses, essential for web applications.

SQL Query: Aggregate and Filter Data with GROUP BY and HAVING

Summarize data using aggregate functions and `GROUP BY`, then filter these aggregated results with `HAVING` for powerful reporting and analytics.

Retrieve Orders with Customer Details from Last 30 Days

Fetch recent customer orders, joining customer and order tables with a date-based filter for active or recent transactions.