Cacher is the code snippet organizer for pro developers

WITH 

dataset as (
  SELECT
    ap_mac,        /* MAC del AP */
    client_mac,
    dt,/* MAC del dispositivo */
    CASE 
      WHEN count(*) > 4 AND AVG(rssi) > 12 THEN 'visitor' ELSE 'passerby'
    END as type
    /* Identificación como "visitor" o "passerby" */
  FROM 
    cmx.raw
  /* Limitamos la información por tenant y por la ultima hora */
  WHERE
    tenant = 'tata'
  AND
   dt like '2018-10-24T05'
  GROUP BY 
    1, 2, 3
)
SELECT 
 client_mac,
 dt,
 case when type= 'visitor' then 1 end as visitor,
 case when type= 'passerby' then 1 end as passerby
FROM 
dataset
GROUP BY
1,2,3,4