refcodes.sql

with snippets as (
  select
    substr(body, instr(body, 'refcode=') + 8, 128) as snippet
  from
    emails
  where
    body LIKE '%refcode%'
),
refcodes as (
  select
    snippet,
    substr(
      snippet,
      0,
      min(
        case
          when instr(snippet, '&') > 0 then instr(snippet, '&')
          else 128
        end,
        case
          when instr(snippet, ']') > 0 then instr(snippet, ']')
          else 128
        end,
        case
          when instr(snippet, ' ') > 0 then instr(snippet, ' ')
          else 128
        end,
        case
          when instr(snippet, '.') > 0 then instr(snippet, '.')
          else 128
        end
      )
    ) as refcode
  from
    snippets
)
select
  refcode,
  count(*) as n
from
  refcodes
group by
  refcode
order by
  n desc