How SQL Helps You Land a Cloud Engineer Job Faster

-- PostgreSQL on RDS: read-only user for a reporting service
CREATE ROLE reporting_ro LOGIN PASSWORD 'use-a-secret-manager';
GRANT CONNECT ON DATABASE shop TO reporting_ro;
GRANT USAGE ON SCHEMA public TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO reporting_ro;

-- Who is connected and what are they doing?
SELECT usename, application_name, state, count(*) AS sessions
FROM pg_stat_activity
GROUP BY usename, application_name, state
ORDER BY sessions DESC;

-- Athena: recent delete actions recorded by CloudTrail
-- (assumes a cloudtrail_logs table created from the CloudTrail S3 path)
SELECT eventtime, eventsource, eventname,
       useridentity.arn AS who, sourceipaddress
FROM cloudtrail_logs
WHERE eventname LIKE 'Delete%'
ORDER BY eventtime DESC
LIMIT 50;

-- Athena: 5xx errors per minute from ALB logs
SELECT date_trunc('minute', from_iso8601_timestamp(time)) AS minute,
       count(*) AS errors
FROM alb_logs
WHERE elb_status_code >= 500
GROUP BY 1
ORDER BY 1;

-- Column names follow the legacy CUR schema; check yours with SHOW COLUMNS.
SELECT line_item_product_code AS service,
       round(sum(line_item_unblended_cost), 2) AS cost
FROM cur_table
WHERE line_item_usage_start_date >= date_trunc('month', current_date)
GROUP BY 1
ORDER BY cost DESC
LIMIT 10;

SELECT line_item_product_code AS service,
       date_trunc('month', line_item_usage_start_date) AS month,
       round(sum(line_item_unblended_cost), 2) AS cost
FROM cur_table
WHERE line_item_usage_start_date >= date_add('month', -2, date_trunc('month', current_date))
GROUP BY 1, 2
ORDER BY 1, 2;

-- PostgreSQL: largest tables (useful before storage alarms)
SELECT relname AS table_name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- After restoring a snapshot to a test instance,
-- compare key counts with production figures you recorded.
SELECT 'orders' AS tbl, count(*) FROM orders
UNION ALL
SELECT 'customers', count(*) FROM customers
UNION ALL
SELECT 'payments', count(*) FROM payments;

Runbook note: "Database connections exhausted"
1. Run the pg_stat_activity query, group by application
2. Identify the app holding most sessions
3. Check that app's recent deploy and connection pool settings
4. Consider a connection pooler such as RDS Proxy
5. Record findings and time in the incident log