Below are some interesting analysis SQL queries against the card database

Beware: Running these queries may take a few minutes to complete.

Cards with updated, localized card names.

The query returns all card names of non-English cards that changed their translated name in newer sets. The filter against english_names removes all cards for which the card name is missing and replaced with their English name as a placeholder.

WITH english_names (name) AS (
    SELECT card_name
    FROM FaceName
    JOIN PrintLanguage USING (language_id)
    WHERE "language" = 'en')

SELECT oracle_id, "language", P1.card_name, P1.set_name, P2.card_name, P2.set_name
  FROM AllPrintings AS P1
  JOIN AllPrintings AS P2 USING ("language", oracle_id, face_number, is_front)
  WHERE P1.scryfall_id < P2.scryfall_id
    AND P1.card_name <> P2.card_name
    AND P1.card_name NOT IN (SELECT name FROM english_names)
    AND P2.card_name NOT IN (SELECT name FROM english_names)
  GROUP BY P1.card_name, P2.card_name
  ORDER BY P1.language, P1.card_name;

Two or more distinct Cards having the same name

Sometimes, two different cards get translated to the same name in a given language, probably due to independent translation teams. As of writing this, the query returns 128 results. The first column creates a Scryfall search link that shows the list of clashing cards.

WITH english_names(name) AS (
    SELECT card_name
    FROM FaceName
    JOIN PrintLanguage USING (language_id)
    WHERE "language" = 'en')

SELECT printf('https://scryfall.com/search?q=l:%s+!%%22%s%%22&unique=prints&as=checklist',
              language, replace(card_name, ' ', '%20')) AS scryfall_search,
    language, card_name, P1.set_name, P1.oracle_id, P2.set_name, P2.oracle_id
  FROM AllPrintings AS P1
  JOIN AllPrintings AS P2 USING ("language", card_name, face_number, is_front)
  WHERE P1.oracle_id < P2.oracle_id
  AND P1.scryfall_id < P2.scryfall_id
  AND card_name NOT IN (SELECT name FROM english_names)
  GROUP BY "language", card_name
  ORDER BY language, card_name;

Numerical image URI parameters

The image URIs contain a numerical parameter, like https://cards.scryfall.io/png/front/8/3/835d47f1-943f-41c3-93f7-2995d7a74684.png?1678945009

There are some duplicates, but most are unique. This query extracts those parameters, and orders the AllPrintings view by duplicate count and numerical parameter:

WITH counted_number(num, cnt) AS (
  SELECT substr(png_image_uri, instr(png_image_uri, '?')+1) AS num, count(*) AS cnt
  FROM CardFace
  GROUP BY num
)
SELECT counted_number.*, AllPrintings.*
  FROM AllPrintings 
  JOIN counted_number ON (substr(png_image_uri, instr(png_image_uri, '?')+1) = num)
  ORDER BY cnt DESC, num ASC;