Hierarchical data is data that references itself. An employee has a manager, who is also an employee. A category has a parent category, which is also a category. A part is made of components, which are themselves parts. A comment has a parent comment, which is also a comment. The relationship is recursive: the same…
A single Common Table Expression is useful. Two CTEs that reference each other are a pipeline. The first CTE produces an intermediate result, the second consumes it and produces another, and the main query consumes the second. This chaining is what turns a CTE from a readability convenience into a query-organization tool. Each CTE is…
A Common Table Expression is a named temporary result set that exists for the duration of a single query. It is defined with the WITH keyword, and it can be referenced by name anywhere in the query that follows. The CTE is not a view — it is not stored in the database schema —…
A GROUP BY clause with a single set of columns produces one row per distinct combination of those columns. That is the standard aggregation behavior, and it answers a specific question: “what is the total for each group?” But analytical queries often need more than one answer at once. A report might need the total…
A window function computes a value across a set of rows related to the current row. The OVER clause defines that set. In the simplest form, the set is the entire partition, and the function sees every row in the group. But many analytical questions require a more precise definition: “the running total so far,”…
Sorting JSON keys by hand is pointless work. But version control diffs need consistency. Without sorted keys, every commit shows changes that aren’t real changes. You add a field and suddenly the entire file looks different. I built a free tool that solves this. What it does: Use cases: Privacy:100% client-side. Nothing uploaded. Try it:…
Compression reduces the size of a file by encoding its contents more efficiently. On Linux, three compressors dominate: gzip, bzip2, and xz. Each is a general-purpose compressor with a different trade-off between speed and compression ratio. gzip is the fastest and the most widely supported. bzip2 compresses better but is slower. xz compresses the best…
tar is the standard archiving tool on Linux. It bundles many files and directories into a single archive file, preserving their paths, permissions, ownership, timestamps, and directory structure. The name comes from “tape archive,” a reference to its origins as a tool for writing backups to magnetic tape, but the format survived the tape era…
awk is a complete programming language designed for text processing. Where grep selects lines and sed transforms them, awk understands fields. It splits each input line into columns, gives each column a name, and lets you write expressions and actions that operate on those columns. A single awk command can find the lines that match…
sed is the stream editor. It reads input line by line, applies a set of editing commands to each line, and writes the result to standard output. Unlike an interactive editor, sed is not meant for hand-editing a file. It is a filter: it takes a stream of text, transforms it according to a script,…