Immuta Actions and Activities: Snowflake SQL references
Immuta Activity: 'High Cardinality' SQLs in Snowflake
query is executed by the datasource ingestion Snowflake user
query is not executed by Immuta <db name>_SYSTEM_ACCOUNTuser.
SELECT"ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "GENDER", "RACE", "SSN", "DEPT", "JOB", "SKILLS", "SALARY", "TYPE", "BRAND_ID"FROM"BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"LIMIT1000;--this is SQL referenced to high cardinality job against the table being ingested to ImmutaSELECTapprox_count_distinct("ID") AS approx FROM"BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR";
Immuta Activity: Fingerprint job SQL(s) on Snowflake
query is executed by the datasource ingestion Snowflake user
query is not executed by Immuta <db name>_SYSTEM_ACCOUNTuser.
Fingerprint is only needed for few Immuta Policy type, e.g.,
Format Preserving Masking
K-Anonymization
Randomized Response
Recommend to run Fingerprint on datasources planned to use the above data-policies.
Immuta Fingerprint SQL-1 in Snowflake:Immuta Fingerprint SQL-2 in Snowflake:Immuta Fingerprint SQL-3 in Snowflake: Immuta Fingerprint SQL-4 in Snowflake:Immuta Fingerprint SQL-5 Snowflake:
Immuta Activity: NativeSDD job in Snowflake:
query is executed by the datasource ingestion Snowflake user
query is not executed by Immuta <db name>_SYSTEM_ACCOUNTuser.
Immuta NativeSDD SQL in Snowflake:
Immuta Activity: when onboarding datasource to Immuta from Snowflake:
query is executed by the datasource ingestion Snowflake user
query is not executed by Immuta <db name>_SYSTEM_ACCOUNTuser.
Enhanced Onboarding is different since high level metadata(database name, schema name, and table names) already crawled or through object-sync.
Immuta Activities: disable or remove of datasources in Immuta
datasources/tables in Immuta will NOT be deleted.
policy objects will be DROP/UNSET if tables were impacted by row-access or column-masking objects.
SQL activities are performed by the Immuta <db name>_SYSTEM_ACCOUNTuser, which manages the Immuta-Snowflake integration.
SQL various depending on Immuta policies - primarily to remove policy objects and revoke GRANTs to Immuta-created xxx_POLICY_xxx and xxx_USER_xxx roles.
WITH FIRST__NAME_value__count_cte AS (SELECT "FIRST_NAME" AS "FIRST__NAME_value", COUNT("FIRST_NAME") AS "FIRST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "FIRST_NAME"),
LAST__NAME_value__count_cte AS (SELECT "LAST_NAME" AS "LAST__NAME_value", COUNT("LAST_NAME") AS "LAST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "LAST_NAME"),
EMAIL_value__count_cte AS (SELECT "EMAIL" AS "EMAIL_value", COUNT("EMAIL") AS "EMAIL_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "EMAIL"),
GENDER_value__count_cte AS (SELECT "GENDER" AS "GENDER_value", COUNT("GENDER") AS "GENDER_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "GENDER"),
RACE_value__count_cte AS (SELECT "RACE" AS "RACE_value", COUNT("RACE") AS "RACE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "RACE"),
SSN_value__count_cte AS (SELECT "SSN" AS "SSN_value", COUNT("SSN") AS "SSN_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SSN"),
DEPT_value__count_cte AS (SELECT "DEPT" AS "DEPT_value", COUNT("DEPT") AS "DEPT_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "DEPT"),
JOB_value__count_cte AS (SELECT "JOB" AS "JOB_value", COUNT("JOB") AS "JOB_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "JOB"),
SKILLS_value__count_cte AS (SELECT "SKILLS" AS "SKILLS_value", COUNT("SKILLS") AS "SKILLS_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SKILLS"),
TYPE_value__count_cte AS (SELECT "TYPE" AS "TYPE_value", COUNT("TYPE") AS "TYPE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "TYPE"),
FIRST__NAME_frequency__count_cte AS (SELECT "FIRST__NAME_count" AS "FIRST__NAME_frequency", COUNT("FIRST__NAME_count") AS "FIRST__NAME_frequency__count" FROM FIRST__NAME_value__count_cte GROUP BY "FIRST__NAME_count"),
LAST__NAME_frequency__count_cte AS (SELECT "LAST__NAME_count" AS "LAST__NAME_frequency", COUNT("LAST__NAME_count") AS "LAST__NAME_frequency__count" FROM LAST__NAME_value__count_cte GROUP BY "LAST__NAME_count"),
EMAIL_frequency__count_cte AS (SELECT "EMAIL_count" AS "EMAIL_frequency", COUNT("EMAIL_count") AS "EMAIL_frequency__count" FROM EMAIL_value__count_cte GROUP BY "EMAIL_count"),
GENDER_frequency__count_cte AS (SELECT "GENDER_count" AS "GENDER_frequency", COUNT("GENDER_count") AS "GENDER_frequency__count" FROM GENDER_value__count_cte GROUP BY "GENDER_count"),
RACE_frequency__count_cte AS (SELECT "RACE_count" AS "RACE_frequency", COUNT("RACE_count") AS "RACE_frequency__count" FROM RACE_value__count_cte GROUP BY "RACE_count"),
SSN_frequency__count_cte AS (SELECT "SSN_count" AS "SSN_frequency", COUNT("SSN_count") AS "SSN_frequency__count" FROM SSN_value__count_cte GROUP BY "SSN_count"),
DEPT_frequency__count_cte AS (SELECT "DEPT_count" AS "DEPT_frequency", COUNT("DEPT_count") AS "DEPT_frequency__count" FROM DEPT_value__count_cte GROUP BY "DEPT_count"),
JOB_frequency__count_cte AS (SELECT "JOB_count" AS "JOB_frequency", COUNT("JOB_count") AS "JOB_frequency__count" FROM JOB_value__count_cte GROUP BY "JOB_count"),
SKILLS_frequency__count_cte AS (SELECT "SKILLS_count" AS "SKILLS_frequency", COUNT("SKILLS_count") AS "SKILLS_frequency__count" FROM SKILLS_value__count_cte GROUP BY "SKILLS_count"),
TYPE_frequency__count_cte AS (SELECT "TYPE_count" AS "TYPE_frequency", COUNT("TYPE_count") AS "TYPE_frequency__count" FROM TYPE_value__count_cte GROUP BY "TYPE_count"),
combined_counts_cte AS ((
SELECT 'FIRST_NAME' AS "column_name", "FIRST__NAME_frequency" AS "frequency", "FIRST__NAME_frequency__count" AS "frequency_count" FROM FIRST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'FIRST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", "LAST__NAME_frequency" AS "frequency", "LAST__NAME_frequency__count" AS "frequency_count" FROM LAST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", "EMAIL_frequency" AS "frequency", "EMAIL_frequency__count" AS "frequency_count" FROM EMAIL_frequency__count_cte
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", "GENDER_frequency" AS "frequency", "GENDER_frequency__count" AS "frequency_count" FROM GENDER_frequency__count_cte
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'RACE' AS "column_name", "RACE_frequency" AS "frequency", "RACE_frequency__count" AS "frequency_count" FROM RACE_frequency__count_cte
)
UNION ALL
(
SELECT 'RACE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SSN' AS "column_name", "SSN_frequency" AS "frequency", "SSN_frequency__count" AS "frequency_count" FROM SSN_frequency__count_cte
)
UNION ALL
(
SELECT 'SSN' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", "DEPT_frequency" AS "frequency", "DEPT_frequency__count" AS "frequency_count" FROM DEPT_frequency__count_cte
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'JOB' AS "column_name", "JOB_frequency" AS "frequency", "JOB_frequency__count" AS "frequency_count" FROM JOB_frequency__count_cte
)
UNION ALL
(
SELECT 'JOB' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", "SKILLS_frequency" AS "frequency", "SKILLS_frequency__count" AS "frequency_count" FROM SKILLS_frequency__count_cte
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", "TYPE_frequency" AS "frequency", "TYPE_frequency__count" AS "frequency_count" FROM TYPE_frequency__count_cte
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
))
SELECT "column_name", "frequency", "frequency_count" FROM combined_counts_cte ORDER BY "column_name", "frequency_count"
WITH cast_as_text_cte AS (SELECT CAST("ID" AS VARCHAR) AS "ID", "FIRST_NAME", "LAST_NAME", "EMAIL", "GENDER", "RACE", "SSN", "DEPT", "JOB", "SKILLS", CAST("SALARY" AS VARCHAR) AS "SALARY", "TYPE", CAST("BRAND_ID" AS VARCHAR) AS "BRAND_ID" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"),
pivot_cte AS (SELECT "column", "value" FROM cast_as_text_cte UNPIVOT("value" FOR "column" IN ("ID","FIRST_NAME","LAST_NAME","EMAIL","GENDER","RACE","SSN","DEPT","JOB","SKILLS","SALARY","TYPE","BRAND_ID"))),
fpe_filter_cte AS (SELECT "column", "value", REGEXP_REPLACE("value", '[^0-9]', '') AS "numeric", REGEXP_REPLACE("value", '[^a-z]', '') AS "alphaLower", REGEXP_REPLACE("value", '[^A-Z]', '') AS "alphaUpper", REGEXP_REPLACE("value", '[^a-zA-Z]', '') AS "alphaLoweralphaUpper", REGEXP_REPLACE("value", '[^a-zA-Z0-9]', '') AS "alphaLoweralphaUppernumeric" FROM pivot_cte),
cell_replacement_cte AS (SELECT "column", "value", "numeric", "alphaLower", "alphaUpper", "alphaLoweralphaUpper", "alphaLoweralphaUppernumeric", (CASE WHEN (LENGTH("numeric") > 2) THEN ABS(((97 - MOD(CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, (LENGTH("numeric") - 2)) AS NUMBER), 97)) - CAST(SUBSTRING(CAST("numeric" AS VARCHAR), (LENGTH("numeric") - 1), 2) AS NUMBER))) ELSE 1 END) AS "numeric_insee", (CASE WHEN (LENGTH("numeric") = 9) THEN MOD(CAST((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__9", (CASE WHEN (LENGTH("numeric") = 10) THEN MOD(CAST(((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__10", (CASE WHEN (LENGTH("numeric") = 11) THEN MOD(CAST((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__11", (CASE WHEN (LENGTH("numeric") = 12) THEN MOD(CAST(((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__12", (CASE WHEN (LENGTH("numeric") = 13) THEN MOD(CAST((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__13", (CASE WHEN (LENGTH("numeric") = 14) THEN MOD(CAST(((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__14", (CASE WHEN (LENGTH("numeric") = 15) THEN MOD(CAST((((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__15", (CASE WHEN (LENGTH("numeric") = 16) THEN MOD(CAST(((((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 16, 1) AS NUMBER)) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__16", (CASE WHEN (LENGTH("numeric") = 17) THEN MOD(CAST((((((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 16, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__17", (CASE WHEN (LENGTH("numeric") = 18) THEN MOD(CAST(((((((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 16, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 18, 1) AS NUMBER)) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__18", (CASE WHEN (LENGTH("numeric") = 19) THEN MOD(CAST((((((((((((((((((((0 + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 1, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 2, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 3, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 4, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 5, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 6, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 7, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 8, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 9, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 10, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 11, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 12, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 13, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 14, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 15, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 16, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 17, 1) AS NUMBER) * 2) AS NUMBER), 10))) + CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 18, 1) AS NUMBER)) + (FLOOR(((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 19, 1) AS NUMBER) * 2) / 10)) + MOD(CAST((CAST(SUBSTRING(CAST("numeric" AS VARCHAR), 19, 1) AS NUMBER) * 2) AS NUMBER), 10))) AS NUMBER), 10) ELSE 1 END) AS "numeric_luhn__19", (CASE WHEN RLIKE("numeric", '^[0-9]{3}[^A-Za-z0-9]{0,1}[0-9]{2}[^A-Za-z0-9]{0,1}[0-9]{4}$', 'c') THEN 1 ELSE 0 END) AS "US_SocialSecurityNumber_regex", (CASE WHEN RLIKE("numeric", '^[0-9]{12,19}$|^[0-9]{4}[^A-Za-z0-9]{1}[0-9]{4}[^A-Za-z0-9]{1}[0-9]{4}[^A-Za-z0-9]{1}[0-9]{4}$|^[0-9]{4}[^A-Za-z0-9]{1}[0-9]{6}[^A-Za-z0-9]{1}[0-9]{5}$', 'c') THEN 1 ELSE 0 END) AS "CreditCardNumber_regex", (CASE WHEN RLIKE("numeric", '^[0-9]{3}[^A-Za-z0-9]{0,1}[0-9]{3}[^A-Za-z0-9]{0,1}[0-9]{3}$', 'c') THEN 1 ELSE 0 END) AS "CA_SocialInsuranceNumber_regex", (CASE WHEN RLIKE("numeric", '^[0-9A-Z]{1}[^A-Za-z0-9]{0,1}[0-9]{7,8}[^A-Za-z0-9]{0,1}[A-Z0-9]{1}$', 'c') THEN 1 ELSE 0 END) AS "ES_NumeroIdentificacionFiscal_regex", (CASE WHEN RLIKE("numeric", '^[A-Z]{2}[^A-Za-z0-9]{0,1}[0-9]{2}[^A-Za-z0-9]{0,1}[0-9]{2}[^A-Za-z0-9]{0,1}[0-9]{2}[^A-Za-z0-9]{0,1}[A-Z]{1}$', 'c') THEN 1 ELSE 0 END) AS "UK_NationalInsuranceNumber_regex", (CASE WHEN RLIKE("numeric", '^[0-9]{13,15}[^A-Za-z0-9]{0,1}[0-9]{2}$', 'c') THEN 1 ELSE 0 END) AS "FR_INSEENumber_regex", (CASE WHEN RLIKE("value", '^[0-9]+$', 'c') THEN 1 ELSE 0 END) AS "Numeric_regex", (CASE WHEN RLIKE("value", '^[a-z]+$', 'c') THEN 1 ELSE 0 END) AS "LowerCase_regex", (CASE WHEN RLIKE("value", '^[A-Z]+$', 'c') THEN 1 ELSE 0 END) AS "UpperCase_regex", (CASE WHEN RLIKE("value", '^[A-Za-z]+$', 'c') THEN 1 ELSE 0 END) AS "Alphabetic_regex", (CASE WHEN RLIKE("value", '^[A-Za-z0-9]+$', 'c') THEN 1 ELSE 0 END) AS "Alphanumeric_regex" FROM fpe_filter_cte),
fpe_agg_cte AS (SELECT "column", MAX(LENGTH("numeric")) AS "numeric_maxStrLen", MIN(LENGTH("numeric")) AS "numeric_minStrLen", (CASE WHEN (SUM("numeric_insee") = 0) THEN TRUE ELSE FALSE END) AS "numeric_insee__isValid", (CASE WHEN (SUM("numeric_luhn__9") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__9_isValid", (CASE WHEN (SUM("numeric_luhn__10") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__10_isValid", (CASE WHEN (SUM("numeric_luhn__11") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__11_isValid", (CASE WHEN (SUM("numeric_luhn__12") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__12_isValid", (CASE WHEN (SUM("numeric_luhn__13") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__13_isValid", (CASE WHEN (SUM("numeric_luhn__14") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__14_isValid", (CASE WHEN (SUM("numeric_luhn__15") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__15_isValid", (CASE WHEN (SUM("numeric_luhn__16") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__16_isValid", (CASE WHEN (SUM("numeric_luhn__17") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__17_isValid", (CASE WHEN (SUM("numeric_luhn__18") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__18_isValid", (CASE WHEN (SUM("numeric_luhn__19") = 0) THEN TRUE ELSE FALSE END) AS "numeric_luhn__19_isValid", (CASE WHEN (SUM("US_SocialSecurityNumber_regex") = COUNT("US_SocialSecurityNumber_regex")) THEN TRUE ELSE FALSE END) AS "US_SocialSecurityNumber_regex_isValid", (CASE WHEN (SUM("CreditCardNumber_regex") = COUNT("CreditCardNumber_regex")) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_isValid", (CASE WHEN (SUM("CA_SocialInsuranceNumber_regex") = COUNT("CA_SocialInsuranceNumber_regex")) THEN TRUE ELSE FALSE END) AS "CA_SocialInsuranceNumber_regex_isValid", (CASE WHEN (SUM("ES_NumeroIdentificacionFiscal_regex") = COUNT("ES_NumeroIdentificacionFiscal_regex")) THEN TRUE ELSE FALSE END) AS "ES_NumeroIdentificacionFiscal_regex_isValid", (CASE WHEN (SUM("UK_NationalInsuranceNumber_regex") = COUNT("UK_NationalInsuranceNumber_regex")) THEN TRUE ELSE FALSE END) AS "UK_NationalInsuranceNumber_regex_isValid", (CASE WHEN (SUM("FR_INSEENumber_regex") = COUNT("FR_INSEENumber_regex")) THEN TRUE ELSE FALSE END) AS "FR_INSEENumber_regex_isValid", (CASE WHEN (SUM("Numeric_regex") = COUNT("Numeric_regex")) THEN TRUE ELSE FALSE END) AS "Numeric_regex_isValid", (CASE WHEN (SUM("LowerCase_regex") = COUNT("LowerCase_regex")) THEN TRUE ELSE FALSE END) AS "LowerCase_regex_isValid", (CASE WHEN (SUM("UpperCase_regex") = COUNT("UpperCase_regex")) THEN TRUE ELSE FALSE END) AS "UpperCase_regex_isValid", (CASE WHEN (SUM("Alphabetic_regex") = COUNT("Alphabetic_regex")) THEN TRUE ELSE FALSE END) AS "Alphabetic_regex_isValid", (CASE WHEN (SUM("Alphanumeric_regex") = COUNT("Alphanumeric_regex")) THEN TRUE ELSE FALSE END) AS "Alphanumeric_regex_isValid" FROM cell_replacement_cte GROUP BY "column")
SELECT "column", (CASE WHEN (("US_SocialSecurityNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 11) AND ("numeric_minStrLen" >= 9))) THEN TRUE ELSE FALSE END) AS "US_SocialSecurityNumber_regex_result", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__12_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_12_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__13_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_13_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__14_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_14_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__15_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_15_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__16_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_16_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__17_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_17_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__18_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_18_isValid", (CASE WHEN ((("CreditCardNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 12))) AND ("numeric_luhn__19_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CreditCardNumber_regex_luhn_19_isValid", (CASE WHEN ((("CA_SocialInsuranceNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 11) AND ("numeric_minStrLen" >= 9))) AND ("numeric_luhn__9_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CA_SocialInsuranceNumber_regex_luhn_9_isValid", (CASE WHEN ((("CA_SocialInsuranceNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 11) AND ("numeric_minStrLen" >= 9))) AND ("numeric_luhn__10_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CA_SocialInsuranceNumber_regex_luhn_10_isValid", (CASE WHEN ((("CA_SocialInsuranceNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 11) AND ("numeric_minStrLen" >= 9))) AND ("numeric_luhn__11_isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "CA_SocialInsuranceNumber_regex_luhn_11_isValid", (CASE WHEN (("ES_NumeroIdentificacionFiscal_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 11) AND ("numeric_minStrLen" >= 9))) THEN TRUE ELSE FALSE END) AS "ES_NumeroIdentificacionFiscal_regex_result", (CASE WHEN (("UK_NationalInsuranceNumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 13) AND ("numeric_minStrLen" >= 9))) THEN TRUE ELSE FALSE END) AS "UK_NationalInsuranceNumber_regex_result", (CASE WHEN ((("FR_INSEENumber_regex_isValid" = TRUE) AND (("numeric_maxStrLen" <= 19) AND ("numeric_minStrLen" >= 15))) AND ("numeric_insee__isValid" = 'TRUE')) THEN TRUE ELSE FALSE END) AS "FR_INSEENumber_regex_insee_isValid", (CASE WHEN ("Numeric_regex_isValid" = TRUE) THEN TRUE ELSE FALSE END) AS "Numeric_regex_result", (CASE WHEN ("LowerCase_regex_isValid" = TRUE) THEN TRUE ELSE FALSE END) AS "LowerCase_regex_result", (CASE WHEN ("UpperCase_regex_isValid" = TRUE) THEN TRUE ELSE FALSE END) AS "UpperCase_regex_result", (CASE WHEN ("Alphabetic_regex_isValid" = TRUE) THEN TRUE ELSE FALSE END) AS "Alphabetic_regex_result", (CASE WHEN ("Alphanumeric_regex_isValid" = TRUE) THEN TRUE ELSE FALSE END) AS "Alphanumeric_regex_result" FROM fpe_agg_cte ORDER BY "column"
WITH FIRST__NAME_value__count_cte AS (SELECT "FIRST_NAME" AS "FIRST__NAME_value", COUNT("FIRST_NAME") AS "FIRST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "FIRST_NAME"),
LAST__NAME_value__count_cte AS (SELECT "LAST_NAME" AS "LAST__NAME_value", COUNT("LAST_NAME") AS "LAST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "LAST_NAME"),
EMAIL_value__count_cte AS (SELECT "EMAIL" AS "EMAIL_value", COUNT("EMAIL") AS "EMAIL_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "EMAIL"),
GENDER_value__count_cte AS (SELECT "GENDER" AS "GENDER_value", COUNT("GENDER") AS "GENDER_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "GENDER"),
RACE_value__count_cte AS (SELECT "RACE" AS "RACE_value", COUNT("RACE") AS "RACE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "RACE"),
SSN_value__count_cte AS (SELECT "SSN" AS "SSN_value", COUNT("SSN") AS "SSN_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SSN"),
DEPT_value__count_cte AS (SELECT "DEPT" AS "DEPT_value", COUNT("DEPT") AS "DEPT_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "DEPT"),
JOB_value__count_cte AS (SELECT "JOB" AS "JOB_value", COUNT("JOB") AS "JOB_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "JOB"),
SKILLS_value__count_cte AS (SELECT "SKILLS" AS "SKILLS_value", COUNT("SKILLS") AS "SKILLS_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SKILLS"),
TYPE_value__count_cte AS (SELECT "TYPE" AS "TYPE_value", COUNT("TYPE") AS "TYPE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "TYPE"),
FIRST__NAME_frequency__count_cte AS (SELECT "FIRST__NAME_count" AS "FIRST__NAME_frequency", COUNT("FIRST__NAME_count") AS "FIRST__NAME_frequency__count" FROM FIRST__NAME_value__count_cte GROUP BY "FIRST__NAME_count"),
LAST__NAME_frequency__count_cte AS (SELECT "LAST__NAME_count" AS "LAST__NAME_frequency", COUNT("LAST__NAME_count") AS "LAST__NAME_frequency__count" FROM LAST__NAME_value__count_cte GROUP BY "LAST__NAME_count"),
EMAIL_frequency__count_cte AS (SELECT "EMAIL_count" AS "EMAIL_frequency", COUNT("EMAIL_count") AS "EMAIL_frequency__count" FROM EMAIL_value__count_cte GROUP BY "EMAIL_count"),
GENDER_frequency__count_cte AS (SELECT "GENDER_count" AS "GENDER_frequency", COUNT("GENDER_count") AS "GENDER_frequency__count" FROM GENDER_value__count_cte GROUP BY "GENDER_count"),
RACE_frequency__count_cte AS (SELECT "RACE_count" AS "RACE_frequency", COUNT("RACE_count") AS "RACE_frequency__count" FROM RACE_value__count_cte GROUP BY "RACE_count"),
SSN_frequency__count_cte AS (SELECT "SSN_count" AS "SSN_frequency", COUNT("SSN_count") AS "SSN_frequency__count" FROM SSN_value__count_cte GROUP BY "SSN_count"),
DEPT_frequency__count_cte AS (SELECT "DEPT_count" AS "DEPT_frequency", COUNT("DEPT_count") AS "DEPT_frequency__count" FROM DEPT_value__count_cte GROUP BY "DEPT_count"),
JOB_frequency__count_cte AS (SELECT "JOB_count" AS "JOB_frequency", COUNT("JOB_count") AS "JOB_frequency__count" FROM JOB_value__count_cte GROUP BY "JOB_count"),
SKILLS_frequency__count_cte AS (SELECT "SKILLS_count" AS "SKILLS_frequency", COUNT("SKILLS_count") AS "SKILLS_frequency__count" FROM SKILLS_value__count_cte GROUP BY "SKILLS_count"),
TYPE_frequency__count_cte AS (SELECT "TYPE_count" AS "TYPE_frequency", COUNT("TYPE_count") AS "TYPE_frequency__count" FROM TYPE_value__count_cte GROUP BY "TYPE_count"),
combined_counts_cte AS ((
SELECT 'FIRST_NAME' AS "column_name", "FIRST__NAME_frequency" AS "frequency", "FIRST__NAME_frequency__count" AS "frequency_count" FROM FIRST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'FIRST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", "LAST__NAME_frequency" AS "frequency", "LAST__NAME_frequency__count" AS "frequency_count" FROM LAST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", "EMAIL_frequency" AS "frequency", "EMAIL_frequency__count" AS "frequency_count" FROM EMAIL_frequency__count_cte
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", "GENDER_frequency" AS "frequency", "GENDER_frequency__count" AS "frequency_count" FROM GENDER_frequency__count_cte
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'RACE' AS "column_name", "RACE_frequency" AS "frequency", "RACE_frequency__count" AS "frequency_count" FROM RACE_frequency__count_cte
)
UNION ALL
(
SELECT 'RACE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SSN' AS "column_name", "SSN_frequency" AS "frequency", "SSN_frequency__count" AS "frequency_count" FROM SSN_frequency__count_cte
)
UNION ALL
(
SELECT 'SSN' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", "DEPT_frequency" AS "frequency", "DEPT_frequency__count" AS "frequency_count" FROM DEPT_frequency__count_cte
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'JOB' AS "column_name", "JOB_frequency" AS "frequency", "JOB_frequency__count" AS "frequency_count" FROM JOB_frequency__count_cte
)
UNION ALL
(
SELECT 'JOB' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", "SKILLS_frequency" AS "frequency", "SKILLS_frequency__count" AS "frequency_count" FROM SKILLS_frequency__count_cte
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", "TYPE_frequency" AS "frequency", "TYPE_frequency__count" AS "frequency_count" FROM TYPE_frequency__count_cte
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
))
SELECT "column_name", "frequency", "frequency_count" FROM combined_counts_cte ORDER BY "column_name", "frequency_count"
WITH stats_cte AS (SELECT "ID" AS "ID", (MIN("ID") OVER ()) AS "ID_min", (MAX("ID") OVER ()) AS "ID_max", ((LEAD("ID", 2) OVER (ORDER BY "ID")) - "ID") AS "ID_binDiff", "SALARY" AS "SALARY", (MIN("SALARY") OVER ()) AS "SALARY_min", (MAX("SALARY") OVER ()) AS "SALARY_max", ((LEAD("SALARY", 2) OVER (ORDER BY "SALARY")) - "SALARY") AS "SALARY_binDiff", "BRAND_ID" AS "BRAND_ID", (MIN("BRAND_ID") OVER ()) AS "BRAND__ID_min", (MAX("BRAND_ID") OVER ()) AS "BRAND__ID_max", ((LEAD("BRAND_ID", 2) OVER (ORDER BY "BRAND_ID")) - "BRAND_ID") AS "BRAND__ID_binDiff" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR")
SELECT COUNT("ID") AS "ID_size", (PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY "ID") - PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY "ID")) AS "ID_iqr", ANY_VALUE("ID_min") AS "ID_min", ANY_VALUE("ID_max") AS "ID_max", (CASE WHEN (COUNT(DISTINCT("ID")) >= 2) THEN MIN("ID_binDiff") ELSE 1 END) AS "ID_minBinSize", COUNT("SALARY") AS "SALARY_size", (PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY "SALARY") - PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY "SALARY")) AS "SALARY_iqr", ANY_VALUE("SALARY_min") AS "SALARY_min", ANY_VALUE("SALARY_max") AS "SALARY_max", (CASE WHEN (COUNT(DISTINCT("SALARY")) >= 2) THEN MIN("SALARY_binDiff") ELSE 1 END) AS "SALARY_minBinSize", COUNT("BRAND_ID") AS "BRAND__ID_size", (PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY "BRAND_ID") - PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY "BRAND_ID")) AS "BRAND__ID_iqr", ANY_VALUE("BRAND__ID_min") AS "BRAND__ID_min", ANY_VALUE("BRAND__ID_max") AS "BRAND__ID_max", (CASE WHEN (COUNT(DISTINCT("BRAND_ID")) >= 2) THEN MIN("BRAND__ID_binDiff") ELSE 1 END) AS "BRAND__ID_minBinSize" FROM stats_cte
WITH FIRST__NAME_value__count_cte AS (SELECT "FIRST_NAME" AS "FIRST__NAME_value", COUNT("FIRST_NAME") AS "FIRST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "FIRST_NAME"),
LAST__NAME_value__count_cte AS (SELECT "LAST_NAME" AS "LAST__NAME_value", COUNT("LAST_NAME") AS "LAST__NAME_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "LAST_NAME"),
EMAIL_value__count_cte AS (SELECT "EMAIL" AS "EMAIL_value", COUNT("EMAIL") AS "EMAIL_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "EMAIL"),
GENDER_value__count_cte AS (SELECT "GENDER" AS "GENDER_value", COUNT("GENDER") AS "GENDER_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "GENDER"),
RACE_value__count_cte AS (SELECT "RACE" AS "RACE_value", COUNT("RACE") AS "RACE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "RACE"),
SSN_value__count_cte AS (SELECT "SSN" AS "SSN_value", COUNT("SSN") AS "SSN_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SSN"),
DEPT_value__count_cte AS (SELECT "DEPT" AS "DEPT_value", COUNT("DEPT") AS "DEPT_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "DEPT"),
JOB_value__count_cte AS (SELECT "JOB" AS "JOB_value", COUNT("JOB") AS "JOB_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "JOB"),
SKILLS_value__count_cte AS (SELECT "SKILLS" AS "SKILLS_value", COUNT("SKILLS") AS "SKILLS_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "SKILLS"),
TYPE_value__count_cte AS (SELECT "TYPE" AS "TYPE_value", COUNT("TYPE") AS "TYPE_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR" WHERE (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NOT NULL) GROUP BY "TYPE"),
FIRST__NAME_frequency__count_cte AS (SELECT "FIRST__NAME_count" AS "FIRST__NAME_frequency", COUNT("FIRST__NAME_count") AS "FIRST__NAME_frequency__count" FROM FIRST__NAME_value__count_cte GROUP BY "FIRST__NAME_count"),
LAST__NAME_frequency__count_cte AS (SELECT "LAST__NAME_count" AS "LAST__NAME_frequency", COUNT("LAST__NAME_count") AS "LAST__NAME_frequency__count" FROM LAST__NAME_value__count_cte GROUP BY "LAST__NAME_count"),
EMAIL_frequency__count_cte AS (SELECT "EMAIL_count" AS "EMAIL_frequency", COUNT("EMAIL_count") AS "EMAIL_frequency__count" FROM EMAIL_value__count_cte GROUP BY "EMAIL_count"),
GENDER_frequency__count_cte AS (SELECT "GENDER_count" AS "GENDER_frequency", COUNT("GENDER_count") AS "GENDER_frequency__count" FROM GENDER_value__count_cte GROUP BY "GENDER_count"),
RACE_frequency__count_cte AS (SELECT "RACE_count" AS "RACE_frequency", COUNT("RACE_count") AS "RACE_frequency__count" FROM RACE_value__count_cte GROUP BY "RACE_count"),
SSN_frequency__count_cte AS (SELECT "SSN_count" AS "SSN_frequency", COUNT("SSN_count") AS "SSN_frequency__count" FROM SSN_value__count_cte GROUP BY "SSN_count"),
DEPT_frequency__count_cte AS (SELECT "DEPT_count" AS "DEPT_frequency", COUNT("DEPT_count") AS "DEPT_frequency__count" FROM DEPT_value__count_cte GROUP BY "DEPT_count"),
JOB_frequency__count_cte AS (SELECT "JOB_count" AS "JOB_frequency", COUNT("JOB_count") AS "JOB_frequency__count" FROM JOB_value__count_cte GROUP BY "JOB_count"),
SKILLS_frequency__count_cte AS (SELECT "SKILLS_count" AS "SKILLS_frequency", COUNT("SKILLS_count") AS "SKILLS_frequency__count" FROM SKILLS_value__count_cte GROUP BY "SKILLS_count"),
TYPE_frequency__count_cte AS (SELECT "TYPE_count" AS "TYPE_frequency", COUNT("TYPE_count") AS "TYPE_frequency__count" FROM TYPE_value__count_cte GROUP BY "TYPE_count"),
combined_counts_cte AS ((
SELECT 'FIRST_NAME' AS "column_name", "FIRST__NAME_frequency" AS "frequency", "FIRST__NAME_frequency__count" AS "frequency_count" FROM FIRST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'FIRST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("FIRST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", "LAST__NAME_frequency" AS "frequency", "LAST__NAME_frequency__count" AS "frequency_count" FROM LAST__NAME_frequency__count_cte
)
UNION ALL
(
SELECT 'LAST_NAME' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("LAST_NAME" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", "EMAIL_frequency" AS "frequency", "EMAIL_frequency__count" AS "frequency_count" FROM EMAIL_frequency__count_cte
)
UNION ALL
(
SELECT 'EMAIL' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("EMAIL" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", "GENDER_frequency" AS "frequency", "GENDER_frequency__count" AS "frequency_count" FROM GENDER_frequency__count_cte
)
UNION ALL
(
SELECT 'GENDER' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("GENDER" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'RACE' AS "column_name", "RACE_frequency" AS "frequency", "RACE_frequency__count" AS "frequency_count" FROM RACE_frequency__count_cte
)
UNION ALL
(
SELECT 'RACE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("RACE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SSN' AS "column_name", "SSN_frequency" AS "frequency", "SSN_frequency__count" AS "frequency_count" FROM SSN_frequency__count_cte
)
UNION ALL
(
SELECT 'SSN' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SSN" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", "DEPT_frequency" AS "frequency", "DEPT_frequency__count" AS "frequency_count" FROM DEPT_frequency__count_cte
)
UNION ALL
(
SELECT 'DEPT' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("DEPT" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'JOB' AS "column_name", "JOB_frequency" AS "frequency", "JOB_frequency__count" AS "frequency_count" FROM JOB_frequency__count_cte
)
UNION ALL
(
SELECT 'JOB' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("JOB" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", "SKILLS_frequency" AS "frequency", "SKILLS_frequency__count" AS "frequency_count" FROM SKILLS_frequency__count_cte
)
UNION ALL
(
SELECT 'SKILLS' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("SKILLS" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", "TYPE_frequency" AS "frequency", "TYPE_frequency__count" AS "frequency_count" FROM TYPE_frequency__count_cte
)
UNION ALL
(
SELECT 'TYPE' AS "column_name", -1 AS "frequency", COALESCE(SUM((CASE WHEN (COALESCE(NULLIF(CAST("TYPE" AS VARCHAR), ''), NULL) IS NULL) THEN 1 ELSE 0 END)), 0) AS "frequency_count" FROM "BC_DATA"."POV_DATA_0717"."IMMUTA_FAKEHR"
))
SELECT "column_name", "frequency", "frequency_count" FROM combined_counts_cte ORDER BY "column_name", "frequency_count"
