text: import report_utilities as utils
  
  from datetime import date
  
  SOURCE_TABLE = 'smart_columbus_cve__captcha'
  
  rlr_query = f"""
      select lane_red_light as red_light_run, bsm_timestamp as last_seen, id, sourcedevice, laneid, speed, lat, long
      from smart_columbus_cve__cve_red_light_runs 
  """
  
  warn_query = f"""
      with 
          time_last_warned as (
              select min(bsm_timestamp) as first_warned, min_by(lat, bsm_timestamp) as lat, min_by(long, bsm_timestamp) as long, min_by(speed, bsm_timestamp) as speed, min_by(heading, bsm_timestamp) as heading, id, sourcedevice, laneid 
              from smart_columbus_cve__cve_red_light_run_warning_issuance
              where warning_issued = true
              group by id, sourcedevice, laneid
          ),
  
          aggregated_records as (
              select bool_or(warning_issued) as warning_issued, max(bsm_timestamp) as last_seen, id, sourcedevice, laneid 
              from smart_columbus_cve__cve_red_light_run_warning_issuance
              group by id, sourcedevice, laneid
          )
  
      select * from aggregated_records left outer join time_last_warned using (id, sourcedevice, laneid)
  """
  
  join_query = f"""
      with 
          rlr as ({rlr_query}),
          warn as ({warn_query})
      select  id, 
              sourcedevice, 
              laneid, 
              red_light_run, 
              rlr.speed * 2.23694 / 50 as speed_at_cross_bar,
              rlr.lat as lat_at_cross_bar,
              rlr.long as long_at_cross_bar,
              warning_issued, 
              rlr.last_seen as rlr_report_last_seen, 
              warn.last_seen as warning_report_last_seen, 
              warn.first_warned as first_received_warning, 
              warn.lat as warning_lat, 
              warn.long as warning_long, 
              warn.speed as warning_speed, 
              heading as warning_heading 
      from rlr join warn using (id, sourcedevice, laneid)
  """
  
  cur = utils.new_connection()
  utils.run_and_save_query(cur, join_query, "cve_warning_effectiveness")