gistfile1.sql

-- DEVELOP ( in staging )
-- Doesn't join with beacon table

SELECT
   COALESCE (SUM(results.sightings),
   0) AS sightings 
FROM
   ( SELECT
      COUNT( raw_events_final.event_type = 'beacon_sighting' 
      OR NULL ) AS sightings  
   FROM
      raw_events_final  
   WHERE
      raw_events_final.organization_ids_idx_0 IN (
         '2iVHZMgQaz5J9P2QVPVXyB'
      ) 
      AND raw_events_final.datasnap_created BETWEEN '2015-01-24T08:00:00.000Z' AND '2015-02-03T07:59:59.999Z') AS results
      
-- LOCAL
-- Does join with beacon table

SELECT
   empty_set.organization_id ,
   COUNT( raw_results.event_type = 'beacon_sighting' 
   OR NULL ) AS sightings  
FROM
   raw_events_final AS raw_results 
RIGHT JOIN
   (
      SELECT
         DISTINCT beacon.name,
         beacon.identifier,
         beacon.organization_id 
      From
         beacon  
      WHERE
         beacon.organization_id IN (
            '2iVHZMgQaz5J9P2QVPVXyB'
         )
   ) AS empty_set 
      ON empty_set.organization_id = raw_results.organization_ids_idx_0 
      AND empty_set.identifier = raw_results.beacon_identifier 
      AND datasnap_created BETWEEN '2015-01-24T08:00:00.000Z' AND '2015-02-03T07:59:59.999Z' 
GROUP BY
   empty_set.organization_id