SQL Skills That Every Analyst Needs

SQL is the most important skill in a data professional's toolkit. Yet many analysts stop at basic SELECT-FROM-WHERE queries. Here are the advanced SQL patterns that will separate you from the crowd.

Window Functions

Window functions let you perform calculations across rows related to the current row — without collapsing the result set. The most useful ones:

• ROW_NUMBER() — deduplicate data by assigning a rank to each row within a partition
• LAG() / LEAD() — access previous or next row values (perfect for calculating day-over-day changes)
• SUM() OVER — running totals and cumulative sums

Example: "Show each order and the running total of revenue for that customer."

SELECT
  order_id,
  customer_id,
  revenue,
  SUM(revenue) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders

Common Table Expressions (CTEs)

CTEs make complex queries readable by breaking them into named steps. They're the SQL equivalent of functions.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(revenue) AS total_revenue
  FROM orders
  GROUP BY 1
),
prev_month AS (
  SELECT *,
    LAG(total_revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT *,
  ROUND((total_revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM prev_month

Pivoting Data

Transform rows into columns for cleaner reporting. In PostgreSQL, use the crosstab function from the tablefunc extension. In BigQuery or Snowflake, use PIVOT:

SELECT *
FROM sales
PIVOT(SUM(revenue) FOR product_category IN ('Electronics', 'Clothing', 'Food'))

Practical Tips

Index your join keys. A query that runs in 2 seconds with proper indexes might take 2 minutes without them. Explain your queries — use EXPLAIN ANALYZE to see where time is spent. Test with representative data volumes — a query that works fast on 100 rows may fall over on 10 million.