# sql

Recursive CTE for Hierarchical Data

Query hierarchical data like org charts, categories, and file trees using recursive CTEs.

Best for: Querying organizational hierarchies

#sql#cte

SQL Pivot and Crosstab Queries

Transform row data into columnar reports using CASE expressions, FILTER, and crosstab patterns.

Best for: Creating monthly revenue reports

#sql#pivot

SQL Upsert and Merge Patterns

Insert or update records atomically using ON CONFLICT, MERGE, and database-specific upsert syntax.

Best for: Syncing data from external sources

#sql#upsert

SQL Index Strategy Patterns

Create effective indexes including composite, partial, covering, and expression-based indexes.

Best for: Optimizing slow database queries

#sql#indexes

SQL Date and Time Functions

Essential date/time operations for filtering, formatting, calculating intervals, and time zones.

Best for: Filtering records by relative date ranges

#sql#dates

SQL String Manipulation Functions

Common string operations for cleaning, formatting, searching, and transforming text data in SQL.

Best for: Data cleaning and normalization

#sql#strings

SQL Materialized View Pattern

Create and manage materialized views for caching expensive queries with refresh strategies.

Best for: Caching expensive analytics queries

#sql#materialized-view

SQL LATERAL Join Examples

Use LATERAL joins to run correlated subqueries for top-N per group and row-dependent lookups.

Best for: Top-N per group queries efficiently

#sql#lateral

SQL Transaction Isolation Levels

Understand and use transaction isolation levels to control concurrency and data consistency.

Best for: Preventing race conditions in financial transactions

#sql#transactions

SQL EXPLAIN ANALYZE for Query Tuning

Use EXPLAIN ANALYZE to understand query plans, identify bottlenecks, and optimize slow queries.

Best for: Diagnosing and fixing slow database queries

#sql#explain

Handle NULLs with COALESCE

Use COALESCE to replace NULL values with a default in SQL queries.

Best for: Replacing NULL values in reports

#sql#coalesce

Self Join for Hierarchical Data

Use a self join to query hierarchical relationships like org charts.

Best for: Org chart queries

#sql#self-join

LATERAL Join for Correlated Subqueries

Use LATERAL join to reference columns from preceding tables in subqueries.

Best for: Top-N per group queries

#sql#lateral

MERGE / UPSERT Statement

Use MERGE or INSERT ON CONFLICT to upsert rows in a single statement.

Best for: Syncing data from staging tables

#sql#merge

GROUPING SETS, CUBE, and ROLLUP

Generate multiple levels of aggregation in a single query with grouping sets.

Best for: Report subtotals

#sql#grouping-sets

Query JSON Columns in SQL

Extract and filter data stored in JSON columns using SQL JSON functions.

Best for: Querying semi-structured data

#sql#json

Create and Refresh Materialized Views

Use materialized views to cache expensive query results for fast reads.

Best for: Dashboard caching

#sql#materialized-view

NTILE and Percentile Window Functions

Distribute rows into buckets and compute percentiles with window functions.

Best for: Customer segmentation

#sql#ntile

Generate a Date Series

Create a continuous date range to fill gaps in time-series queries.

Best for: Filling gaps in time-series data

#sql#date-series

Read EXPLAIN ANALYZE Output

Use EXPLAIN ANALYZE to understand and optimize query execution plans.

Best for: Query performance tuning

#sql#explain

Aggregate Strings with STRING_AGG / GROUP_CONCAT

Concatenate values from multiple rows into a single string per group.

Best for: Comma-separated lists in reports

#sql#string-agg

Temporal Tables for Data Versioning

Track historical changes to rows using system-versioned temporal tables.

Best for: Audit trail

#sql#temporal-tables

Conditional Aggregation with FILTER / CASE

Compute multiple conditional aggregates in a single query using FILTER or CASE.

Best for: Pivot-style reports

#sql#conditional

Window Frame ROWS vs RANGE Clauses

Control exactly which rows a window function considers using frame specifications.

Best for: Moving averages

#sql#window-frame

Common Table Expression (CTE) Patterns

Use CTEs to write readable, composable SQL queries with WITH clauses for complex logic.

Best for: complex queries

#sql#cte

INSERT ... RETURNING for Immediate Results

Use RETURNING clause to get inserted rows immediately without a separate SELECT query.

Best for: getting generated IDs

#sql#insert

CASE WHEN Conditional Expressions

Use CASE expressions for conditional logic in SELECT, WHERE, ORDER BY, and aggregations.

Best for: data categorization

#sql#case-when

Subquery Patterns — Scalar, Correlated, Derived

Common subquery patterns including scalar, correlated, and derived table subqueries.

Best for: complex filtering

#sql#subquery

Array and UNNEST Operations

Work with array columns using UNNEST, ARRAY_AGG, and array operators in PostgreSQL.

Best for: tag systems

#sql#arrays

GRANT and REVOKE Permission Management

Manage database permissions with GRANT and REVOKE for roles, schemas, and tables.

Best for: database security

#sql#permissions

View - Technique 1

Create and manage database views

Best for: database operations

Index - Technique 2

Create and optimize database indexes

Best for: database operations

Trigger - Technique 3

Create triggers for automatic actions

Best for: database operations

Procedure - Technique 4

Create stored procedures

Best for: database operations

Function - Technique 5

Create user-defined functions

Best for: database operations

Cursor - Technique 6

Work with database cursors

Best for: database operations

Transaction - Technique 7

Manage database transactions

Best for: database operations

Lock - Technique 8

Handle database locking

Best for: database operations

Constraint - Technique 9

Add database constraints

Best for: database operations

Sequence - Technique 10

Create and use sequences

Best for: database operations

Partition - Technique 11

Partition large tables

Best for: database operations

JSON - Technique 12

Work with JSON data

Best for: database operations

XML - Technique 13

Parse XML data

Best for: database operations

Date - Technique 14

Handle date functions

Best for: database operations

String - Technique 15

String manipulation functions

Best for: database operations

Math - Technique 16

Mathematical functions

Best for: database operations

Window - Technique 17

Advanced window functions

Best for: database operations

Subquery - Technique 18

Use subqueries

Best for: database operations

Join - Technique 19

Advanced join techniques

Best for: database operations

Aggregate - Technique 20

Aggregate functions

Best for: database operations

Window Analytics - Technique 21

Advanced analytics

Best for: database operations

Materialized View - Technique 22

Materialized views

Best for: database operations

Composite Index - Technique 23

Multi-column indexes

Best for: database operations

Full Text Search - Technique 24

Full text indexing

Best for: database operations

Explain Plan - Technique 25

Query execution plans

Best for: database operations

Deadlock - Technique 26

Handle deadlocks

Best for: database operations

Replication - Technique 27

Database replication

Best for: database operations

Backup - Technique 28

Database backups

Best for: database operations

Restore - Technique 29

Database restoration

Best for: database operations

Archive - Technique 30

Data archiving

Best for: database operations

Vacuum - Technique 31

Database maintenance

Best for: database operations

Analyze - Technique 32

Query analysis

Best for: database operations

Statistics - Technique 33

Table statistics

Best for: database operations

Cluster - Technique 34

Data clustering

Best for: database operations

Sharding - Technique 35

Database sharding

Best for: database operations

Partitioning Strategy - Technique 36

Partitioning patterns

Best for: database operations

Denormalization - Technique 37

Denormalization patterns

Best for: database operations

Normalization - Technique 38

Database normalization

Best for: database operations

ETL Pipeline - Technique 39

Extract Transform Load

Best for: database operations

Data Warehouse - Technique 40

Data warehouse design

Best for: database operations

Recursive CTE - Technique 41

Recursive query traversal

Best for: database operations

Upsert - Technique 42

Insert or update rows

Best for: database operations

Merge - Technique 43

Merge source into target

Best for: database operations

Pivot - Technique 44

Pivot row values into columns

Best for: database operations

Unpivot - Technique 45

Unpivot columns into rows

Best for: database operations

Query Hint - Technique 46

Use optimizer hints

Best for: database operations

GRANT - Technique 47

Grant roles and privileges

Best for: database operations

RLS - Technique 48

Row level security policies

Best for: database operations

Audit - Technique 49

Track data mutations

Best for: database operations

CTE - Technique 50

Reusable query blocks

Best for: database operations

LangChain SQL Database Agent

Create an AI agent that answers natural language questions by querying a SQL database.

Best for: NL2SQL

#langchain#sql

pythonintermediate

LangChain create_sql_query_chain

Generate SQL from natural language using LangChain's create_sql_query_chain with schema awareness.

Best for: NL to SQL

#langchain#nl2sql

Text-to-SQL with Validation Safety

Convert natural language to SQL with LLM and validate queries before execution for safety.

Best for: safe NL2SQL

#nl2sql#sql

Spark SQL Query Example

PySpark DataFrame operations with SQL queries, window functions, and aggregations for big data.

Best for: Processing large-scale datasets with Spark

#spark#pyspark

SQL Incremental Load Pattern

Incremental data load using watermark tracking to process only new and updated records efficiently.

Best for: Efficient warehouse loading without full reloads

#sql#incremental-load

SQL Data Deduplication Techniques

Remove duplicate records using ROW_NUMBER, DISTINCT ON, and self-join deduplication strategies.

Best for: Cleaning duplicate records in production databases

#sql#deduplication

SQL Window Functions for Analytics

Advanced SQL window functions for running totals, rankings, moving averages, and gap analysis.

Best for: Building analytics dashboards with running totals

#sql#window-functions

SQL Schema Migration Pattern

Versioned schema migration scripts with forward and rollback support for database evolution.

Best for: Managing database schema changes across environments

#sql#migration

dbt Model with Tests and Schema

A dbt SQL model with incremental materialization, schema tests, and source freshness checks.

Best for: Building analytics data models with dbt

#dbt#sql

SQL Window Functions for Analytics

Use window functions for running totals, rankings, moving averages, and gap detection in analytics.

Best for: Building cumulative revenue dashboards

#sql#window-functions

SQL Data Quality Checks and Assertions

Reusable SQL queries for data quality: null checks, uniqueness, referential integrity, and freshness.

Best for: Automated data quality gates in ETL pipelines

#sql#data-quality

SQL Running Totals and Cumulative Metrics

Calculate running totals, cumulative counts, and percent-of-total using window functions and partitions.

Best for: Building cumulative revenue dashboards

#sql#window-functions

pythonintermediate

Stream Large SQL Query in Chunks

Read millions of rows from SQL in memory-safe chunks using pandas read_sql with chunksize.

Best for: large table extraction

#pandas#sql