-- Query behind https://studio.mufatech.com/ecommerce-platform-migrations/ (BigQuery, standard SQL).
-- The page publishes only counts; the per-site rows this returns stay private.
-- A migration is A,A -> B,B: the same platform two crawls in a row, then another platform two in a row.
-- kind = 'event': one row per migration (switched_by = first crawl on the new platform).
-- kind = 'stock': number of home pages per platform per crawl, for rates.
WITH pages AS (
  SELECT
    date,
    root_page,
    rank,
    ARRAY(
      SELECT DISTINCT IF(t.technology IN ('Tiendanube', 'Nuvemshop'), 'Nuvemshop', t.technology)
      FROM UNNEST(technologies) t, UNNEST(t.categories) c
      WHERE c = 'Ecommerce' AND t.technology NOT IN (
        'Cart Functionality', 'Hyva Themes', 'EasyDigitalDownloads', 'Dokan', 'Welcart', 'Gumroad',
        'Pimcore', 'Botble CMS', 'eZ Publish', 'PureCars', 'Amazon Webstore', 'Digital Showroom', 'Rain')
    ) AS platforms
  FROM `httparchive.crawl.pages`
  WHERE date BETWEEN DATE '2025-07-01' AND DATE '2026-09-01'
    AND client = 'mobile' AND is_root_page
),
labelled AS (
  SELECT date, root_page, rank,
    CASE ARRAY_LENGTH(platforms) WHEN 0 THEN '-' WHEN 1 THEN platforms[OFFSET(0)] ELSE 'MULTI' END AS label
  FROM pages
),
per_site AS (
  SELECT root_page, ARRAY_AGG(STRUCT(date, label, rank)) AS obs
  FROM labelled
  GROUP BY root_page
  HAVING COUNTIF(label NOT IN ('-', 'MULTI')) >= 2
),
seq AS (
  SELECT root_page, ARRAY(
    SELECT AS STRUCT
      d,
      IFNULL((SELECT o.label FROM UNNEST(obs) o WHERE o.date = d ORDER BY o.label LIMIT 1), 'x') AS label,
      (SELECT MIN(o.rank) FROM UNNEST(obs) o WHERE o.date = d) AS rank
    FROM UNNEST(GENERATE_DATE_ARRAY(DATE '2025-07-01', DATE '2026-09-01', INTERVAL 1 MONTH)) d
    ORDER BY d
  ) AS s
  FROM per_site
)
SELECT 'event' AS kind, root_page, s[OFFSET(k + 1)].d AS month, s[OFFSET(k)].label AS from_platform,
  s[OFFSET(k + 1)].label AS to_platform, s[OFFSET(k)].rank AS rank, 1 AS sites
FROM seq, UNNEST(GENERATE_ARRAY(1, 12)) k
WHERE s[OFFSET(k - 1)].label = s[OFFSET(k)].label
  AND s[OFFSET(k + 1)].label = s[OFFSET(k + 2)].label
  AND s[OFFSET(k)].label != s[OFFSET(k + 1)].label
  AND s[OFFSET(k)].label NOT IN ('-', 'x', 'MULTI')
  AND s[OFFSET(k + 1)].label NOT IN ('-', 'x', 'MULTI')
UNION ALL
SELECT 'stock', CAST(NULL AS STRING), date, label, CAST(NULL AS STRING), CAST(NULL AS INT64), COUNT(DISTINCT root_page)
FROM labelled
WHERE label NOT IN ('-', 'MULTI')
GROUP BY date, label
HAVING COUNT(DISTINCT root_page) >= 100