[specimens_by_year]
SELECT COUNT(*) as f, r.year
FROM request_specimen rs
	JOIN requests r on rs.request_id = r.id
GROUP BY r.year  
ORDER BY r.year

[requests_by_year]
SELECT COUNT(*) as f, year
FROM requests
GROUP BY year  
ORDER BY year
	
[requests_by_month]
SELECT COUNT(*) as f,
    concat(year, '_', date_format(created_at,'%b'))
FROM requests
WHERE %CONSTRAINT%
GROUP BY year,month(created_at)    
ORDER BY year,month(created_at)

[requests_by_day_of_week]
SELECT COUNT(*) as f,
    dayname(created_at)
FROM requests
WHERE %CONSTRAINT%
GROUP BY dayname(created_at)
ORDER BY dayofweek(created_at)

[diagnosis_status]
SELECT COUNT(*) as f,
    if ( status = 'default', 'follow-up', status )
FROM request_report
WHERE %CONSTRAINT%
GROUP BY status
ORDER BY f desc

[presentation_frequency]
SELECT count(*) as f,
    s.description
FROM requests r
    join request_initial_screen ris on (ris.request_id = r.id)
	join screens s on (ris.screen_id = s.id)
WHERE %CONSTRAINT%
GROUP BY s.id
ORDER BY f desc
LIMIT 20

[specimen_frequency]
SELECT count(*) as f,
    s.description
FROM requests r
    join request_specimen rs on (rs.request_id = r.id)
	join specimens s on (rs.specimen_id = s.id)
WHERE %CONSTRAINT%
GROUP BY s.id
ORDER BY f desc
LIMIT 20

[new_diagnosis_frequency]
SELECT count(*) as f,
    t2.name
FROM request_report t1
	join diagnoses t2 on (t1.diagnosis_id = t2.id)
WHERE icdo3 is not null
    and %CONSTRAINT%
    and status = 'new'
GROUP BY t2.id
ORDER BY f desc
LIMIT 20

[diagnosis_frequency]
SELECT count(*) as f,
    t2.name
FROM request_report t1
	join diagnoses t2 on (t1.diagnosis_id = t2.id)
WHERE %CONSTRAINT%
GROUP BY t2.id
ORDER BY f desc
LIMIT 20

