How SQL Skills Boost Your Cloud Engineer Job Search

-- Join orders to users to see who bought what
SELECT
  u.email,
  o.id           AS order_id,
  o.total_amount,
  o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 100;

-- Why cloud engineers care:
-- - Confirms the join column is indexed
-- - Spot the fan-out (1:N) that blows up row counts
-- - Reproduces customer issues with real data

-- Aggregation — error rate by service per hour
SELECT
  service_name,
  date_trunc('hour', occurred_at) AS hour,
  COUNT(*)                        AS total,
  COUNT(*) FILTER (WHERE is_error) AS errors,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE is_error) / COUNT(*),
    2
  ) AS error_pct
FROM request_logs
WHERE occurred_at > NOW() - INTERVAL '24 hours'
GROUP BY service_name, hour
ORDER BY hour DESC, error_pct DESC;

-- Why cloud engineers care:
-- - Dashboards before you build them
-- - Explains cost spikes (log volume × time)
-- - Cheap alternative to a new monitoring tool

-- Window functions — rank instances by CPU without self-joins
SELECT
  instance_id,
  avg_cpu,
  RANK() OVER (
    PARTITION BY account_id
    ORDER BY avg_cpu DESC
  ) AS cpu_rank,
  SUM(cost_usd) OVER (
    PARTITION BY account_id
    ORDER BY collected_at
    ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW
  ) AS running_cost
FROM cloud_metrics
WHERE collected_at >= NOW() - INTERVAL '30 days';

-- Why cloud engineers care:
-- - Rank outliers without extra passes
-- - Running totals for cost dashboards
-- - One query replaces several scripts

-- PostgreSQL on Amazon RDS
-- Top 10 slowest queries in the last hour
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Add an index concurrently (no table lock)
CREATE INDEX CONCURRENTLY
  idx_orders_user_created
ON orders (user_id, created_at DESC);

-- Why this matters:
-- - CONCURRENTLY avoids blocking writes in prod
-- - pg_stat_statements is the first stop for slow queries

-- BigQuery — prune columns, partition-filter, cap cost
SELECT
  user_id,
  SUM(amount) AS revenue
FROM `proj.sales.orders`
WHERE _PARTITIONDATE BETWEEN
      DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
      AND CURRENT_DATE()
GROUP BY user_id;

-- Cost rules:
-- - Never SELECT * on a large table
-- - Always filter on the partition column
-- - Preview the query first (dry run shows bytes scanned)
-- - Use MAX_BYTES_BILLED as a safety net

-- Bad: SELECT * FROM `proj.sales.orders`
-- Good: SELECT user_id, SUM(amount) ... (above)

-- Azure SQL (T-SQL) — read the cached plan
SELECT TOP 10
  qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
  qs.execution_count,
  SUBSTRING(st.text, 1, 200) AS query_snippet
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed DESC;

-- Add an index without a full rebuild
CREATE INDEX IX_orders_user_created
ON dbo.Orders (user_id, created_at DESC)
WITH (ONLINE = ON);

-- Why this matters:
-- - ONLINE = ON keeps reads/writes flowing
-- - DMVs are the Azure SQL equivalent of pg_stat_statements

-- Slow: full scan, no index, fetches every column
SELECT *
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

-- EXPLAIN shows:
--   Seq Scan on orders (cost=0..48210 rows=1 width=…)
--   Filter: (status = 'pending')
--   Sort: created_at DESC

-- Why it is slow:
-- - Seq Scan = reads the whole table
-- - SELECT * pulls columns you never use
-- - No index on (status, created_at)
-- - Sorting the full result set in memory

-- Fast: covered index, only the columns needed, LIMIT
CREATE INDEX CONCURRENTLY
  idx_orders_status_created
ON orders (status, created_at DESC);

SELECT id, total_amount, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;

-- EXPLAIN now shows:
--   Index Scan using idx_orders_status_created (cost=0..18 …)

-- Why it is fast:
-- - Index satisfies filter AND sort
-- - Only 3 columns are read
-- - LIMIT stops the scan early
-- - CONCURRENTLY avoided a write lock during deploy

-- BigQuery equivalent:
--   SELECT id, total_amount, created_at
--   FROM orders
--   WHERE status = 'pending'
--     AND _PARTITIONDATE = CURRENT_DATE()
--   LIMIT 100;

-- 1) Row counts must match on both sides
SELECT 'users'    AS tbl, COUNT(*) FROM users
UNION ALL
SELECT 'orders',         COUNT(*) FROM orders
UNION ALL
SELECT 'order_items',    COUNT(*) FROM order_items;

-- 2) Checksums catch silent changes
SELECT MD5(
  STRING_AGG(
    id::text || '|' || updated_at::text,
    ',' ORDER BY id
  )
) AS users_fingerprint
FROM users;

-- 3) Spot orphaned rows after the copy
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- If these return identical values on source and
-- target, the migration is safe to cut over.

-- Daily revenue rollup into BigQuery
CREATE OR REPLACE TABLE analytics.revenue_daily
PARTITION BY order_date
AS
SELECT
  DATE(created_at)          AS order_date,
  user_id,
  COUNT(*)                  AS orders,
  SUM(total_amount)         AS revenue,
  AVG(total_amount)         AS avg_order_value
FROM raw.orders
WHERE DATE(created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY order_date, user_id;

-- Why use SQL here:
-- - One statement, versioned in Git
-- - Runs on a schedule, idempotent (CREATE OR REPLACE)
-- - No application code, no deploy
-- - Cost is a few MB scanned, not a job hours long

Common cloud interview questions
that touch SQL:

- "A production RDS instance is slow.
   How do you debug it?"
- "How do you keep BigQuery costs down?"
- "How do you validate a database migration?"
- "How do you design a schema for a
   multi-tenant SaaS on Postgres?"
- "When would you pick a NoSQL store
   over a relational one?"

In every case, SQL fluency is what
separates a strong answer from a weak one.

How to answer (example):

Q: "A production RDS instance is slow.
    How do you debug it?"

Weak: "I restart it."

Strong: "I check pg_stat_statements for
the top queries by total_exec_time. If
one query dominates, I pull EXPLAIN and
look for Seq Scans, bad join orders, or
missing indexes. If it is spread across
many queries, I check connection counts
and CPU before resizing. My fix order is
always: index → rewrite → resize."

That answer shows:
- Operational instinct
- SQL fluency
- Cost discipline
- Senior-level thinking