.bail ON
.mode table --wrap 0

SELECT datetime(value, 'unixepoch') 'DB version'
  FROM rb_db_lov
 WHERE key = 'data_timestamp';
+---------------------+
|     DB version      |
+---------------------+
| 2024-09-18 07:55:42 |
+---------------------+

-- For prints, having 'pr' in `part_num`, all suffix formats in a form where every letter and digit are replaced with `x` and `N`. For example, both `3009pr0027e` and `4555c02pr0001a` will have suffix `NNNNx`.

WITH RECURSIVE t(part_num, suffix, i)
    AS (SELECT child_part_num, substr(child_part_num, 2 + instr(child_part_num, 'pr')), 1
          FROM (SELECT * FROM part_relationships UNION ALL SELECT * FROM part_rels_extra)
         WHERE rel_type = 'P'
           AND instr(child_part_num, 'pr') > 1
         UNION ALL
        SELECT part_num, substr(suffix, 1, i - 1) || 'N' || substr(suffix, i + 1), i + 1
          FROM t
         WHERE substr(suffix, i, 1) GLOB '[0-9]'
         UNION ALL
        SELECT part_num, substr(suffix, 1, i - 1) || 'x' || substr(suffix, i + 1), i + 1
          FROM t
         WHERE substr(suffix, i, 1) GLOB '[a-zA-Z]'
       )
SELECT suffix
     , count(DISTINCT part_num) num_part_nums
     , part_num example_part_num
     , 'https://rebrickable.com/parts/' || part_num || '/' part_url
  FROM t
 WHERE length(suffix) = i - 1
 GROUP BY suffix
 ORDER BY num_part_nums DESC;
+---------+---------------+------------------+------------------------------------------------+
| suffix  | num_part_nums | example_part_num |                    part_url                    |
+---------+---------------+------------------+------------------------------------------------+
| NNNN    | 25162         | 3626cpr3662      | https://rebrickable.com/parts/3626cpr3662/     |
| NNNNx   | 10            | 3009pr0027e      | https://rebrickable.com/parts/3009pr0027e/     |
| x       | 4             | 973prh           | https://rebrickable.com/parts/973prh/          |
| N       | 4             | 973pr9           | https://rebrickable.com/parts/973pr9/          |
| NNNNxx  | 1             | 93088pr0002kc    | https://rebrickable.com/parts/93088pr0002kc/   |
| NNNNxNN | 1             | 93088pr0008c01   | https://rebrickable.com/parts/93088pr0008c01/  |
| NNNNN   | 1             | 36187c01pr00556  | https://rebrickable.com/parts/36187c01pr00556/ |
+---------+---------------+------------------+------------------------------------------------+