gistfile1.sql

WITH interm_table AS(
SELECT species.phylum,
species.order_name,
species.spcode,
species.common_name,
species.scientific_name,
COUNT(*) AS no_releases
FROM animal_release
LEFT JOIN animal ON animal_release.animal_id = animal.id
JOIN species ON animal.species_id = species.id
GROUP BY phylum,species.order_name,spcode,common_name,scientific_name
ORDER BY phylum,order_name,spcode,common_name)
SELECT species.phylum,
species.order_name,
species.spcode,	
species.common_name,
species.scientific_name,
no_releases,
COUNT(valid_detection.timestamp) AS no_detections,
date(min(valid_detection.timestamp)) AS first_detection,
date(max(valid_detection.timestamp)) AS last_detection,
date_part('day',max(valid_detection.timestamp)-min(valid_detection.timestamp)) AS coverage_duration,
CASE WHEN COUNT(valid_detection.timestamp)=0 THEN 'No detection'::text END AS missing_info
FROM valid_detection
JOIN detection_surgery ON valid_detection.id = detection_surgery.detection_id
JOIN surgery ON detection_surgery.surgery_id = surgery.id
FULL JOIN animal_release ON surgery.release_id = animal_release.id
LEFT JOIN animal ON animal_release.animal_id = animal.id
JOIN species ON animal.species_id = species.id
JOIN interm_table ON species.spcode = interm_table.spcode
GROUP BY species.phylum,species.order_name,species.spcode,species.common_name,species.scientific_name,no_releases
ORDER BY phylum,order_name,spcode,common_name;