USE fiwiki_p;

SELECT

  CONCAT (

    CASE

      WHEN tl.tl_namespace = 10 THEN 'Malline'

      WHEN tl.tl_namespace = 828 THEN 'Moduuli'

    END,

    ':',

    tl.tl_title

  ) AS 'Page',

  COUNT(*) AS 'Transclusions',

  CASE

    WHEN COUNT(*) < 1000 AND MOD(COUNT(*), 100) < 60

      THEN CONCAT( SUBSTR(COUNT(*), 1, LENGTH(COUNT(*)) - 2), '00' )

    WHEN COUNT(*) < 1000

      THEN CONCAT( SUBSTR(COUNT(*), 1, LENGTH(COUNT(*)) - 2), '50' )

    WHEN COUNT(*) < 10000 AND MOD(COUNT(*), 100) < 60

      THEN CONCAT( SUBSTR(COUNT(*), 1, 1), ',', SUBSTR(COUNT(*), 2, 1), '00' )

    WHEN COUNT(*) < 10000

      THEN CONCAT( SUBSTR(COUNT(*), 1, 1), ',', SUBSTR(COUNT(*), 2, 1), '50' )

    WHEN COUNT(*) < 1000000 AND MOD(COUNT(*), 1000) < 600

      THEN CONCAT( SUBSTR(COUNT(*), 1, LENGTH(COUNT(*)) - 3), ',000' )

    WHEN COUNT(*) < 1000000

      THEN CONCAT( SUBSTR(COUNT(*), 1, LENGTH(COUNT(*)) - 3), ',500' )

    ELSE

      CONCAT( SUBSTR(COUNT(*), 1, 1), '.', SUBSTR(COUNT(*), 2, 1), ' million' )

  END AS 'Over',

  CONCAT (

    CASE

      WHEN tl.tl_namespace = 10 THEN 'Template'

      WHEN tl.tl_namespace = 828 THEN 'Module'

    END,

    ':',

    tl.tl_title,

    '/doc/use-count/doc'

  ) AS 'Counter'

FROM templatelinks tl

WHERE tl.tl_title IN (

  SELECT

    SUBSTR(pg.page_title, 1, LENGTH(pg.page_title) - 18)

  FROM

    page pg

  WHERE

    pg.page_title LIKE '%/doc/use-count/doc'

    AND pg.page_id IN (

      SELECT cl.cl_from

      FROM categorylinks cl

      WHERE cl.cl_to = 'Possible-risk/use-count'

    )

)

AND tl.tl_namespace IN (10, 828)

GROUP BY CONCAT(tl.tl_title, tl.tl_namespace)

HAVING COUNT(*) > 100

ORDER BY COUNT(*) DESC;