Detecting Insurance Fraud Using Graph Algorithms with Neo4j

USE ROLE ACCOUNTADMIN;

-- Create an account role to manage the GDS application
CREATE ROLE IF NOT EXISTS gds_role;
GRANT APPLICATION ROLE neo4j_graph_analytics.app_user TO ROLE gds_role;
GRANT APPLICATION ROLE neo4j_graph_analytics.app_admin TO ROLE gds_role;

--Grant permissions for the application to use the database
GRANT USAGE ON DATABASE i_demo TO APPLICATION neo4j_graph_analytics;
GRANT USAGE ON SCHEMA i_demo.public TO APPLICATION neo4j_graph_analytics;

--Create a database role to manage table and view access
CREATE DATABASE ROLE IF NOT EXISTS gds_db_role;

GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA i_demo.public TO DATABASE ROLE gds_db_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA i_demo.public TO DATABASE ROLE gds_db_role;

GRANT ALL PRIVILEGES ON FUTURE VIEWS IN SCHEMA i_demo.public TO DATABASE ROLE gds_db_role;
GRANT ALL PRIVILEGES ON ALL VIEWS IN SCHEMA i_demo.public TO DATABASE ROLE gds_db_role;

GRANT CREATE TABLE ON SCHEMA i_demo.public TO DATABASE ROLE gds_db_role;


--Grant the DB role to the application and admin user
GRANT DATABASE ROLE gds_db_role TO APPLICATION neo4j_graph_analytics;
GRANT DATABASE ROLE gds_db_role TO ROLE gds_role;

GRANT USAGE ON DATABASE I_DEMO TO ROLE GDS_ROLE;
GRANT USAGE ON SCHEMA I_DEMO.PUBLIC TO ROLE GDS_ROLE;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA I_DEMO.PUBLIC TO ROLE GDS_ROLE;
GRANT CREATE TABLE ON SCHEMA I_DEMO.PUBLIC TO ROLE GDS_ROLE;
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA I_DEMO.PUBLIC TO ROLE GDS_ROLE;

use warehouse NEO4J_GRAPH_ANALYTICS_APP_WAREHOUSE;
use role gds_role;
use database i_demo;
use schema public;

CREATE OR REPLACE TABLE node_policies AS
SELECT DISTINCT policy_number
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE node_police_report_available AS
SELECT DISTINCT police_report_available
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE node_witnesses AS
SELECT DISTINCT witnesses
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE node_auto_make AS
SELECT DISTINCT auto_make
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE node_policy_state AS
SELECT DISTINCT policy_state
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE node_policy_csl AS
SELECT DISTINCT policy_csl
FROM i_demo.public.insurance_claims_full;


CREATE OR REPLACE TABLE policy_states AS
SELECT ROW_NUMBER() OVER (ORDER BY policy_state) AS state_id, policy_state
FROM (
  SELECT DISTINCT policy_state FROM i_demo.public.insurance_claims_full
);

CREATE OR REPLACE TABLE node_total_claim_amount_bucket AS
SELECT DISTINCT
  CASE
    WHEN total_claim_amount < 40000 THEN 'Low'
    WHEN total_claim_amount BETWEEN 40000 AND 70000 THEN 'Medium'
    WHEN total_claim_amount > 70000 THEN 'High'
    ELSE 'Unknown'
  END AS total_claim_amount_bucket
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE node_months_as_customer_bucket AS
SELECT DISTINCT
  CASE
    WHEN months_as_customer < 100 THEN 'Short (<100m)'
    WHEN months_as_customer BETWEEN 100 AND 300 THEN 'Medium (100-300m)'
    WHEN months_as_customer > 300 THEN 'Long (>300m)'
    ELSE 'Unknown'
  END AS months_as_customer_bucket
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE all_nodes AS
SELECT DISTINCT policy_number::STRING AS nodeid FROM node_policies
UNION
SELECT DISTINCT police_report_available::STRING AS nodeid FROM node_police_report_available
UNION
SELECT DISTINCT witnesses::STRING AS nodeid FROM node_witnesses
UNION
SELECT DISTINCT total_claim_amount_bucket::STRING AS nodeid FROM node_total_claim_amount_bucket
UNION
SELECT DISTINCT auto_make::STRING AS nodeid FROM node_auto_make
UNION
SELECT DISTINCT policy_state::STRING AS nodeid FROM node_policy_state
UNION
SELECT DISTINCT policy_csl::STRING AS nodeid FROM node_policy_csl
UNION
SELECT DISTINCT months_as_customer_bucket::STRING AS nodeid FROM node_months_as_customer_bucket;

CREATE OR REPLACE TABLE rel_policy_police_report_available AS
SELECT
  policy_number,
  police_report_available
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_witnesses AS
SELECT
  policy_number,
  witnesses
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_auto_make AS
SELECT
  policy_number,
  auto_make
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_policy_state AS
SELECT
  policy_number,
  policy_state
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_policy_csl AS
SELECT
  policy_number,
  policy_csl
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_total_claim_amount_bucket AS
SELECT
  policy_number,
  CASE
    WHEN total_claim_amount < 40000 THEN 'Low'
    WHEN total_claim_amount BETWEEN 40000 AND 70000 THEN 'Medium'
    WHEN total_claim_amount > 70000 THEN 'High'
    ELSE 'Unknown'
  END AS total_claim_amount_bucket
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE rel_policy_months_as_customer_bucket AS
SELECT
  policy_number,
  CASE
    WHEN months_as_customer < 100 THEN 'Short (<100m)'
    WHEN months_as_customer BETWEEN 100 AND 300 THEN 'Medium (100-300m)'
    WHEN months_as_customer > 300 THEN 'Long (>300m)'
    ELSE 'Unknown'
  END AS months_as_customer_bucket
FROM i_demo.public.insurance_claims_full;

CREATE OR REPLACE TABLE all_relationships AS
SELECT policy_number::STRING AS sourcenodeid, police_report_available::STRING AS targetnodeid
FROM rel_policy_police_report_available
UNION
SELECT policy_number::STRING, witnesses::STRING
FROM rel_policy_witnesses
UNION
SELECT policy_number::STRING, total_claim_amount_bucket::STRING
FROM rel_policy_total_claim_amount_bucket
UNION
SELECT policy_number::STRING, auto_make::STRING
FROM rel_policy_auto_make
UNION
SELECT policy_number::STRING, policy_state::STRING
FROM rel_policy_policy_state
UNION
SELECT policy_number::STRING, policy_csl::STRING
FROM rel_policy_policy_csl
UNION
SELECT policy_number::STRING, months_as_customer_bucket::STRING
FROM rel_policy_months_as_customer_bucket;

CALL Neo4j_Graph_Analytics.graph.fast_rp('CPU_X64_XS', {
  'project': {
    'defaultTablePrefix': 'i_demo.public',
    'nodeTables': ['all_nodes'],
    'relationshipTables': {
      'all_relationships': {
        'sourceTable': 'all_nodes',
        'targetTable': 'all_nodes',
        'orientation': 'UNDIRECTED'

      }
    }
  },
  'compute': {
    'mutateProperty': 'embedding',
    'embeddingDimension': 128,
    'randomSeed': 1234
  },
  'write': [{
    'nodeLabel': 'all_nodes',
    'outputTable': 'i_demo.public.all_nodes_fast_rp',
    'nodeProperty': 'embedding'
  }]
});

SELECT
  nodeid,
  embedding
FROM i_demo.public.all_nodes_fast_rp;

CALL Neo4j_Graph_Analytics.graph.knn('CPU_X64_XS', {
  'project': {
    'defaultTablePrefix': 'i_demo.public',
    'nodeTables': [ 'all_nodes_fast_rp' ],
    'relationshipTables': {}
  },
  'compute': {
    'nodeProperties': ['EMBEDDING'],
    'topK': 3,
    'mutateProperty': 'score',
    'mutateRelationshipType': 'SIMILAR_TO'
  },
  'write': [{
    'outputTable': 'i_demo.public.claims_knn_similarity',
    'sourceLabel': 'all_nodes_fast_rp',
    'targetLabel': 'all_nodes_fast_rp',
    'relationshipType': 'SIMILAR_TO',
    'relationshipProperty': 'score'
  }]
});

SELECT
    score,
    COUNT(*) AS row_count
FROM i_demo.public.claims_knn_similarity
GROUP BY score
ORDER BY score DESC