40 Prompts for SQL, Regex & Shell

A bike-share company’s data team had three people, four hundred stations, and a nightly job that kept failing at 2 a.m. with an unhelpful error. Half their day went to the same three chores: writing joins they had written before, hand-fixing messy station IDs that broke downstream reports, and debugging shell scripts that only failed in production. None of it was hard. All of it was repetitive, and repetition is exactly where a language model earns its keep.

These forty prompts cover the three tools that data teams live in: SQL for asking, regex for cleaning, and the shell for moving files and jobs around. They are grouped by what you are actually doing, not by tool, because in practice a single task often starts in SQL and ends in a shell one-liner. The examples assume a shared-bike fleet with trip, station, and membership tables. For deeper query-building technique, see the SQL query generator prompt and the regex workflow guide.

Matching a Prompt to the Problem

Every prompt below states the tool, the flavor (PostgreSQL, Python, bash), and the expected output shape. The model is only as precise as the schema and samples you paste, so include the column names and three real rows before asking for a query. When a task starts in code you did not write, beginning with an explanation prompt saves you from fixing a query you never understood; that habit is covered in the explain a codebase prompt.

SQL: Modeling and Exploration

1. Schema orientation. The fastest way to onboard a new teammate.

You are a data engineer. Explain this schema to a new teammate: [SCHEMA]. Name each table, the grain of each, and the join keys between them. Flag any ambiguous column.

2. Table design. Gets the grain right before the first row lands.

Design a PostgreSQL table to store bike trips at the grain of one trip. Give columns, types, and the primary key, and flag anything that should live in a separate table.

3. Query from plain English. You describe the question, it writes the SQL.

Write a PostgreSQL query: for each station, the number of trips starting there in [MONTH]. Use tables [SCHEMA]. Return station name and count, ordered descending.

4. Window function. Ranking within a group, explained.

Write a query that ranks stations by daily trip starts within each city using a window function. Explain the partition and the order, and note any ties.

5. Weekly active riders. Cohort-style reporting without guesswork.

Write a query that shows weekly active riders by membership type for the last 12 weeks. Define active as at least one trip in the week.

6. Signup-to-first-trip funnel. Counts the conversions that matter.

Write a query that counts sign-ups who made a first trip within 7 days, using tables [SCHEMA]. Output the count, the percentage, and the median time to first trip.

7. Data quality scan. Finds the rows that will break a dashboard.

Write a query that finds trips whose end time is before the start time, and trips longer than 24 hours. Return the trip id and both timestamps for each.

SQL: Performance and Debugging

8. Read a query plan. Turns EXPLAIN output into one action.

Here is an EXPLAIN ANALYZE output: [PASTE]. Tell me the slowest node, why it is slow, and the single change most likely to help.

9. Index suggestion. Weighs the read gain against the write cost.

This query is slow: [QUERY]. The table has [ROWS] rows. Suggest the index or indexes that would help, and explain the tradeoff on insert performance.

10. Rewrite for speed. Same result, fewer scanned rows.

Rewrite this query to avoid a full table scan: [QUERY]. Keep the identical result set and explain what changed and why it is faster.

11. N+1 detection. Catches the loop that fires a thousand queries.

Here is the ORM code: [CODE]. Tell me whether it causes N+1 queries, and if so, show the single query that would replace the loop.

12. Deadlock diagnosis. For the job that fails only at night.

Two queries deadlock intermittently: [QUERY A] and [QUERY B]. Explain the likely lock-order problem and give the smallest change that removes it.

13. Correctness review. The NULL that silently drops rows.

Review this query for correctness bugs, especially NULL handling in joins and WHERE filters: [QUERY]. Point to the exact line where a row could be dropped by mistake.

Regex: Building and Testing Patterns

14. Pattern to English. Understand the pattern before you edit it.

Explain this regex in plain English, token by token: [REGEX]. Include one string it matches and one it does not.

15. English to pattern. From a description to a working pattern.

Write a regex that matches [DESCRIPTION], for example bike IDs like [EXAMPLE]. Use the Python flavor and explain each part of the pattern.

16. Test case set. Ten strings that prove the pattern works.

Here is a regex: [REGEX]. Write ten test strings: five that should match and five that should not, including two tricky edge cases you choose.

17. Named groups. Makes the match readable six months later.

Rewrite this regex to use named groups so the code is readable: [REGEX]. Keep the same matches and show how to read each group in Python.

18. Anchoring check. Partial match or full match, decided explicitly.

Does this regex match the whole string or only part of it? [REGEX] Explain anchoring and show how to force a full-string match.

19. Backtracking risk. The pattern that freezes on a big log.

This regex is slow on large log files: [REGEX]. Explain the backtracking risk and give a faster version with the same matches.

20. Flavor port. Moving a pattern without breaking it.

Port this regex from Python to [LANGUAGE]: [REGEX]. Note any flavor differences that could change what it matches or misses.

Regex: Cleaning Messy Data

21. Log parsing. Pulls structure out of unstructured lines.

Write a regex to extract timestamp, station ID, and error code from these log lines: [SAMPLES]. Show the capture groups and a Python example.

22. Email normalisation. Fixes casing and spacing before the join.

Write a regex to normalise station contact emails: strip whitespace, lowercase the domain, and reject any without a dot in the domain.

23. Broken CSV fields. Quoted commas that split the wrong way.

These CSV fields are malformed: [SAMPLES]. Write a regex to clean the quoted commas without breaking valid rows, and flag any row you cannot fix.

24. Phone to E.164. One format for the whole support table.

Write a regex and replacement to convert these support phone formats into E.164: [SAMPLES]. Show the pattern, the replacement, and one example output.

25. Split versus regex. Chooses the safer parsing method.

These fields need splitting on [DELIMITER]. Tell me whether a regex split or a fixed delimiter split is safer here, then write the safer option.

26. Batch validity count. How many lines break the pattern.

Write a shell pipeline that counts how many lines in [FILE] do not match [PATTERN] using grep and wc. Explain each step of the pipeline.

Shell: Pipelines and One-Liners

27. CSV top-N. A one-liner you can read, not a magic spell.

Write a shell one-liner that counts trips per station from [FILE].csv, sorts descending, and prints the top 20. Explain every stage of the pipeline.

28. awk aggregation. Group and sum without loading a database.

Write an awk command that sums the trip duration column grouped by membership type in [FILE].csv. Assume a header row and explain the block structure.

29. Find and clean. Delete only after a dry run.

Write a shell command to find files older than [DAYS] in [DIR], list them first, and delete only after I confirm. Show the dry-run version separately.

30. Safe rename. Batch rename that shows its plan.

Write a command to rename files matching [PATTERN] in a directory. Show the dry-run version that prints old and new names before any file is moved.

31. JSON to CSV line. jq that outputs something a spreadsheet reads.

Write a jq command to extract [FIELD] from this JSON structure and print one CSV line per record: [SAMPLE]. Explain the path syntax.

32. Parallel batch run. Eight jobs at once, failures logged.

Write a command that runs [SCRIPT] over 200 files with xargs using eight parallel jobs, and writes any failures to a log file with the filename.

33. Filtered log follow. Watch one keyword, ignore the noise.

Write a command that follows a log file and prints only lines containing [KEYWORD], with a timestamp prefixed to each line.

Shell: Operations and Troubleshooting

34. Exit code bug. The pipeline that lies about failure.

This pipeline returns a non-zero exit code even when it succeeds: [PIPELINE]. Explain why, and fix it so a failing stage fails the whole job.

35. Quoting audit. The bug that only appears with spaces in names.

Review this script for quoting bugs that break on spaces in filenames: [SCRIPT]. Fix them and explain each change in one line.

36. Cron check. Confirms a schedule before it surprises you.

Explain this cron expression and tell me when it next runs: [CRON]. Flag any overlap with our nightly job at [TIME].

37. Disk full triage. For the 2 a.m. job that died.

Our data job failed with "no space left on device". Given this output: [PASTE], tell me what to check first and the safe cleanup order.

38. Permission fix. Least privilege, not chmod 777.

Explain this permission error: [ERROR]. Give the least-privilege fix rather than chmod 777, and explain what access the process actually needs.

39. Environment diff. What changed between working and failing.

Compare these two environment variable dumps from a working and a failing run: [PASTE]. Tell me what changed and which change most likely caused the failure.

40. Destructive command review. A second pair of eyes before you press enter.

I am about to run this command: [COMMAND]. List what could go wrong, and give a dry-run version I can check before anything is changed.

Bonus Plays: Guardrails for Data Work

Always paste the schema and three real rows before asking for a query, because a model that guesses column names produces SQL that runs but returns the wrong number. Ask for a dry-run form of every destructive shell command and read it before you run the real one. Keep generated regexes next to their ten test strings, so the next person can re-verify the pattern in seconds. Run new SQL against a copy, never production. And treat an EXPLAIN output as the source of truth over a model’s guess about why a query is slow.

Sources & Notes

These prompts were written for a three-person data engineering team at a shared-bike company, but the three tools map onto any team that queries, cleans, and schedules data. Prompts 1 through 7 build and explore. Prompts 8 through 13 fix queries that are slow or wrong. Prompts 14 through 20 build and test patterns. Prompts 21 through 26 clean messy fields. Prompts 27 through 33 write pipelines. Prompts 34 through 40 keep a live system running.

Two cautions. Never let a model write directly to a production database, and never run a generated destructive command without a dry run. If a prompt returns SQL you do not fully understand, ask it to explain the query line by line before you trust the result, because a wrong number that looks right is more dangerous than an obvious error.