SQL for AI Analytics

-- The anatomy of a SELECT statement (execute in this order)
SELECT   column1, column2, AGG_FUNCTION(column3)  -- 6. What to return
FROM     table_name                                 -- 1. Where to look
JOIN     other_table ON table.id = other_table.id  -- 2. Join data
WHERE    condition                                  -- 3. Filter rows
GROUP BY column1, column2                          -- 4. Group results
HAVING   AGG_FUNCTION(column3) > threshold         -- 5. Filter groups
ORDER BY column1 DESC                              -- 7. Sort
LIMIT    100;                                      -- 8. Limit rows

Window functions perform calculations across a set of rows related to the current row — without collapsing rows like GROUP BY does:

-- Running total, rank, lag/lead
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rank,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue,1) OVER (ORDER BY date) AS day_over_day
FROM sales;

Common Table Expressions (CTEs) make complex queries readable by breaking them into named steps:

WITH
applicants AS (
  SELECT user_id, applied_at FROM applications
),
screened AS (
  SELECT user_id FROM phone_screens
),
offered AS (
  SELECT user_id FROM offers
)
SELECT
  COUNT(DISTINCT a.user_id) AS total_applicants,
  COUNT(DISTINCT s.user_id) AS screened,
  COUNT(DISTINCT o.user_id) AS offered,
  ROUND(COUNT(DISTINCT s.user_id)::numeric /
        COUNT(DISTINCT a.user_id) * 100, 1) AS screen_rate_pct
FROM applicants a
LEFT JOIN screened s USING (user_id)
LEFT JOIN offered o USING (user_id);

AI dramatically accelerates SQL development. Effective pattern: describe your table schemas and the question you’re trying to answer, then ask the AI to write the query. Always review and test before running in production — AI sometimes generates syntactically valid but logically incorrect queries. After getting a query, ask the AI to explain it clause by clause, then try to write the next query yourself before asking for help.