.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 |
+---------------------+

-- Use "color popularity" coefficient as total number of parts in this color per year normalized by color.

CREATE TEMPORARY VIEW color_popularity
AS
  SELECT 1 + CAST(round(99.0 * log(max(num_parts) OVER (PARTITION BY year), num_parts)) AS INTEGER) popularity
       , *
    FROM (SELECT sum(quantity) num_parts, *
            FROM ___set_parts_for_stats
           GROUP BY color_id, year
         )
    JOIN colors c
      ON c.id = color_id;

-- Print it for all colors in 2018.

SELECT name
     , num_parts
     , popularity
  FROM color_popularity
 WHERE year = 2018
 ORDER BY num_parts DESC;
+--------------------------+-----------+------------+
|           name           | num_parts | popularity |
+--------------------------+-----------+------------+
| Black                    | 28892     | 100        |
| Light Bluish Gray        | 24830     | 99         |
| White                    | 17918     | 95         |
| Dark Bluish Gray         | 15834     | 94         |
| Tan                      | 10001     | 90         |
| Red                      | 9151      | 89         |
| Reddish Brown            | 8387      | 88         |
| Blue                     | 6281      | 85         |
| Yellow                   | 5042      | 83         |
| Dark Tan                 | 3362      | 79         |
| Green                    | 3001      | 78         |
| Orange                   | 2842      | 78         |
| Dark Red                 | 2771      | 77         |
| Dark Blue                | 2666      | 77         |
| Pearl Gold               | 2304      | 76         |
| Trans-Clear              | 2107      | 75         |
| Lime                     | 1814      | 73         |
| Trans-Light Blue         | 1757      | 73         |
| Flat Silver              | 1665      | 72         |
| Bright Light Orange      | 1292      | 70         |
| Dark Azure               | 1240      | 70         |
| Medium Nougat            | 1141      | 69         |
| Dark Purple              | 1089      | 68         |
| Medium Azure             | 1050      | 68         |
| Dark Green               | 1019      | 68         |
| Trans-Red                | 934       | 67         |
| Sand Green               | 858       | 66         |
| Bright Pink              | 771       | 65         |
| Trans-Orange             | 746       | 65         |
| Dark Pink                | 726       | 64         |
| Trans-Yellow             | 700       | 64         |
| Dark Brown               | 653       | 63         |
| Magenta                  | 635       | 63         |
| Bright Green             | 610       | 63         |
| Medium Blue              | 570       | 62         |
| Dark Orange              | 557       | 62         |
| Medium Lavender          | 514       | 61         |
| Dark Turquoise           | 500       | 61         |
| Olive Green              | 436       | 60         |
| Trans-Brown              | 395       | 59         |
| Light Nougat             | 363       | 58         |
| Light Aqua               | 350       | 57         |
| Pearl Titanium           | 348       | 57         |
| [No Color/Any Color]     | 338       | 57         |
| Sand Blue                | 335       | 57         |
| Bright Light Yellow      | 326       | 57         |
| Trans-Dark Pink          | 324       | 57         |
| Trans-Neon Orange        | 321       | 57         |
| Lavender                 | 299       | 56         |
| Trans-Bright Green       | 296       | 56         |
| Trans-Dark Blue          | 249       | 54         |
| Trans-Purple             | 222       | 53         |
| Trans-Green              | 206       | 52         |
| Bright Light Blue        | 154       | 50         |
| Metallic Silver          | 151       | 49         |
| Trans-Neon Green         | 139       | 49         |
| Yellowish Green          | 76        | 43         |
| Metallic Gold            | 43        | 37         |
| Nougat                   | 33        | 35         |
| Chrome Silver            | 24        | 32         |
| Glow in Dark White       | 23        | 31         |
| Pearl Copper             | 11        | 24         |
| Trans-Medium Blue        | 9         | 22         |
| Chrome Gold              | 9         | 22         |
| Glitter Trans-Purple     | 5         | 17         |
| Glitter Trans-Neon Green | 2         | 8          |
| Chrome Pink              | 1         | 1          |
+--------------------------+-----------+------------+

-- Print it for White and Red for all years.

SELECT year
     , w.popularity white
     , r.popularity red
     , substr(CASE
                WHEN w.popularity > r.popularity
                THEN printf('%.*c', r.popularity, '=') || printf('%.*c', w.popularity - r.popularity, '-')
                WHEN w.popularity < r.popularity
                THEN printf('%.*c', w.popularity, '=') || printf('%.*c', r.popularity - w.popularity, '_')
                ELSE printf('%.*c', w.popularity, '=')
              END, 60, 41) bar
  FROM color_popularity w
  JOIN color_popularity r
 USING (year)
 WHERE w.name = 'White'
   AND r.name = 'Red'
 ORDER BY year DESC;
+------+-------+-----+-------------------------------------------+
| year | white | red |                    bar                    |
+------+-------+-----+-------------------------------------------+
| 2024 | 95    | 89  | ==============================------      |
| 2023 | 97    | 89  | ==============================--------    |
| 2022 | 96    | 90  | ===============================------     |
| 2021 | 98    | 88  | =============================----------   |
| 2020 | 94    | 88  | =============================------       |
| 2019 | 97    | 89  | ==============================--------    |
| 2018 | 95    | 89  | ==============================------      |
| 2017 | 96    | 88  | =============================--------     |
| 2016 | 94    | 87  | ============================-------       |
| 2015 | 94    | 88  | =============================------       |
| 2014 | 93    | 88  | =============================-----        |
| 2013 | 94    | 89  | ==============================-----       |
| 2012 | 94    | 90  | ===============================----       |
| 2011 | 95    | 91  | ================================----      |
| 2010 | 98    | 93  | ==================================-----   |
| 2009 | 94    | 92  | =================================--       |
| 2008 | 98    | 90  | ===============================--------   |
| 2007 | 93    | 93  | ==================================        |
| 2006 | 91    | 91  | ================================          |
| 2005 | 92    | 91  | ================================-         |
| 2004 | 97    | 93  | ==================================----    |
| 2003 | 95    | 88  | =============================-------      |
| 2002 | 88    | 89  | =============================_            |
| 2001 | 89    | 88  | =============================-            |
| 2000 | 88    | 90  | =============================__           |
| 1999 | 92    | 91  | ================================-         |
| 1998 | 93    | 94  | ==================================_       |
| 1997 | 91    | 94  | ================================___       |
| 1996 | 96    | 91  | ================================-----     |
| 1995 | 93    | 94  | ==================================_       |
| 1994 | 93    | 94  | ==================================_       |
| 1993 | 95    | 91  | ================================----      |
| 1992 | 94    | 91  | ================================---       |
| 1991 | 96    | 98  | =====================================__   |
| 1990 | 100   | 99  | ========================================- |
| 1989 | 92    | 90  | ===============================--         |
| 1988 | 95    | 88  | =============================-------      |
| 1987 | 97    | 96  | =====================================-    |
| 1986 | 97    | 95  | ====================================--    |
| 1985 | 93    | 99  | ==================================______  |
| 1984 | 85    | 89  | ==========================____            |
| 1983 | 95    | 94  | ===================================-      |
| 1982 | 99    | 99  | ========================================  |
| 1981 | 95    | 100 | ====================================_____ |
| 1980 | 85    | 96  | ==========================___________     |
| 1979 | 94    | 94  | ===================================       |
| 1978 | 94    | 100 | ===================================______ |
| 1977 | 93    | 100 | ==================================_______ |
| 1976 | 96    | 99  | =====================================___  |
| 1975 | 85    | 100 | ==========================_______________ |
| 1974 | 97    | 100 | ======================================___ |
| 1973 | 96    | 100 | =====================================____ |
| 1972 | 93    | 100 | ==================================_______ |
| 1971 | 97    | 100 | ======================================___ |
| 1970 | 98    | 100 | =======================================__ |
| 1969 | 96    | 100 | =====================================____ |
| 1968 | 95    | 100 | ====================================_____ |
| 1967 | 94    | 100 | ===================================______ |
| 1966 | 100   | 100 | ========================================= |
| 1965 | 100   | 95  | ====================================----- |
| 1964 | 93    | 100 | ==================================_______ |
| 1963 | 72    | 76  | =============____                         |
| 1962 | 100   | 93  | ==================================------- |
| 1961 | 100   | 97  | ======================================--- |
| 1960 | 100   | 93  | ==================================------- |
| 1959 | 68    | 100 | =========________________________________ |
| 1958 | 100   | 87  | ============================------------- |
| 1957 | 100   | 89  | ==============================----------- |
| 1956 | 100   | 91  | ================================--------- |
| 1955 | 100   | 94  | ===================================------ |
| 1954 | 98    | 100 | =======================================__ |
| 1953 | 100   | 100 | ========================================= |
| 1949 | 100   | 90  | ===============================---------- |
+------+-------+-----+-------------------------------------------+