
-- create table of all offense and officerf pairs
DROP TABLE IF EXISTS public.dallaspolice_offense_officer;
CREATE FACT TABLE public.dallaspolice_offense_officer
DISTRIBUTE BY HASH(offense)
AS
SELECT offenseservicenumber offense, offensereportofficerbadge1 officer 
  FROM public.dallaspolice
 WHERE offensereportofficerbadge1 is not null
UNION 
SELECT offenseservicenumber offense, offensereportingofficerbadge2 officer 
  FROM public.dallaspolice
 WHERE offensereportingofficerbadge2 is not null;

-- compute MBA to derive edges with weights
DROP TABLE IF EXISTS public.dallaspolice_offense_officer_mba;
SELECT * FROM cfilter (
  ON (SELECT 1)
  PARTITION BY 1
  INPUTTABLE('public.dallaspolice_offense_officer')
  OUTPUTTABLE('public.dallaspolice_offense_officer_mba')
  INPUTCOLUMNS('officer')
  JOINCOLUMNS('offense')
  DROPTABLE('true')
);

-- police officer graph vertices
DROP TABLE IF EXISTS public.dallaspolice_officer_vertices;
CREATE FACT TABLE public.dallaspolice_officer_vertices
DISTRIBUTE BY HASH(officer)
AS
SELECT distinct col1_item1 officer, cnt1 offense_count FROM public.dallaspolice_offense_officer_mba
UNION 
SELECT distinct col1_item2 officer, cnt2 offense_count FROM public.dallaspolice_offense_officer_mba;

-- police officer undirected graph edges with weights 
-- (using average confidence in both direction) 
DROP TABLE IF EXISTS public.dallaspolice_officer_edges_un;
CREATE FACT TABLE public.dallaspolice_officer_edges_un
DISTRIBUTE BY HASH(officer1)
AS
SELECT DISTINCT CASE WHEN mba1.col1_item1 < mba1.col1_item2 THEN mba1.col1_item1
                     ELSE mba1.col1_item2 
                END officer1, 
                CASE WHEN mba1.col1_item1 < mba1.col1_item2 THEN mba1.col1_item2
                     ELSE mba1.col1_item1 
                END officer2,
                (mba1.confidence + mba2.confidence)/2.0 weight
  FROM public.dallaspolice_offense_officer_mba mba1 JOIN
       public.dallaspolice_offense_officer_mba mba2 ON (mba1.col1_item1 = mba2.col1_item2 and mba1.col1_item2 = mba2.col1_item1);

-- police officer directed graph edges with weights
-- (using confidence in one direction)
DROP TABLE IF EXISTS public.dallaspolice_officer_edges_di;
CREATE FACT TABLE public.dallaspolice_officer_edges_di
DISTRIBUTE BY HASH(officer1)
AS
SELECT DISTINCT col1_item1 officer1, col1_item2 officer2, confidence weight
  FROM public.dallaspolice_offense_officer_mba;