#14:

SELECT COUNT(*) FROM (
SELECT
  ticket,
  newvalue
FROM ticket_change
WHERE field = 'comment' AND (
   newvalue LIKE '=%NO%PATCH%NO%FIX%' OR
   newvalue LIKE '=%!%=' OR
   newvalue LIKE '% SPAMMER %' OR
   newvalue LIKE '% TROLL %' OR
   newvalue LIKE '%Where is your patch?%' OR
   newvalue LIKE '%<a href=%' OR
   newvalue LIKE '%[url=%'
)
GROUP BY ticket ORDER BY time DESC, ticket DESC
) AS tab



#13:

SELECT COUNT(*) FROM (
SELECT p.value AS __color__,
  id AS ticket, summary, component, version, milestone,
  (CASE status WHEN 'assigned' THEN owner || ' *' ELSE owner END) AS owner,
  (CASE WHEN STRFTIME('%s','now') - (changetime / 1000000) > 86400
        THEN CAST((STRFTIME('%s','now') - (changetime / 1000000))/86400 AS INTEGER) || ' days'
        WHEN STRFTIME('%s','now') - (changetime / 1000000) > 3600
        THEN CAST((STRFTIME('%s','now') - (changetime / 1000000))/3600 AS INTEGER) || ' hours'
        ELSE CAST((STRFTIME('%s','now') - (changetime / 1000000))/60 AS INTEGER) || ' mins'
  END) AS Modified,
  (CASE WHEN author LIKE '%@%' THEN SUBSTR(author, 0, 3) || '...' ELSE author END) AS Modified_By,
  description AS _description,
  reporter AS _reporter
FROM
  ticket t, enum p,
  (SELECT ticket, author, MAX(time) FROM ticket_change WHERE field = 'comment' GROUP BY ticket) tc
WHERE status IN ('new', 'assigned', 'reopened')
  AND p.name = t.priority AND p.type = 'priority'
  AND t.id = tc.ticket
ORDER BY changetime desc, p.value, milestone, time

) AS tab



#6:

SELECT COUNT(*) FROM (

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN ''||%s||'' THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version, t.type AS type, severity, priority, owner,
   changetime AS modified,
   time AS _time,reporter AS _reporter
  FROM ticket t,enum p
  WHERE p.name=t.priority AND p.type='priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN modified ELSE -p.value END) DESC

) AS tab