How many
claims do we have?

1700618484.9229593

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim;

select * from {{
semantic_layer.query(metrics=['claims'])
}}

{
"measures": [
"Claim.count_of_claims"
],
"total": true,
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700618531.6098642

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier, SUM(Loss_Payment.Claim_Amount + Loss_Reserve.Claim_Amount) AS Total_Loss_Amounts
FROM Claim
LEFT JOIN Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier
LEFT JOIN Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount', 'total_loss_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNTS
0 1 36800
1 2 72000

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT TOTAL_LOSS_AMOUNT
0 12312701 1200 1000 2200
1 12312702 2300 2100 4400

{
"measures": [
"Loss_Payment.total_loss_payment"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
},

{
"measures": [
"Loss_Reserve.total_loss_reserve"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
}

Correct (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
],
"orderBy": {
"metric": {
"name": "loss_payment_amount"
},
"descending": true
}
}

True (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
],
"orderBy": {
"metric": {
"name": "loss_payment_amount"
},
"descending": true
}
}

Correct (Calculated)

What is the average time to
settle a claim by policy number?

1700618569.8109136

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT
Policy.Policy_Number,
AVG(DATEDIFF(day, Claim.Claim_Open_Date, Claim.Claim_Close_Date)) AS Average_Days_To_Settle
FROM
Claim
INNER JOIN
Claim_Coverage ON Claim.Claim_Identifier = Claim_Coverage.Claim_Identifier
INNER JOIN
Policy_Coverage_Detail ON Claim_Coverage.Policy_Coverage_Detail_Identifier = Policy_Coverage_Detail.Policy_Coverage_Detail_Identifier
INNER JOIN
Policy ON Policy_Coverage_Detail.Policy_Identifier = Policy.Policy_Identifier
WHERE
Claim.Claim_Close_Date IS NOT NULL
GROUP BY
Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_DAYS_TO_SETTLE
0 31003000336 20.500000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

{
"measures": [
"Claim.average_time_to_settle_claim"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700618606.4701831

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Policy_Count) as Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) as Total_Premium, COUNT(DISTINCT Policy_Identifier) as Policy_Count
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) as SubQuery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVG_POLICY_SIZE
0 2849000.000000000000

{
"measures": [
"Policy.count_of_policies",
"Premium.total_premium"
],
"responseFormat": "default",
"dimensions": []
}

Correct (Calculated)

How many policies have
agents sold by agent id?

1700618640.2964075

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Agreement_Party_Role.Party_Identifier AS Agent_ID, COUNT(DISTINCT Policy.Policy_Identifier) AS Number_of_Policies_Sold
FROM Agreement_Party_Role
JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Agreement_Party_Role.Party_Role_Code = 'Agent'
GROUP BY Agreement_Party_Role.Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

Empty DataFrame
Columns: [AGENT_ID, NUMBER_OF_POLICIES_SOLD]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"AG"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

What is the total amount of
premiums that a policy holder has paid?

1700618675.3918896

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 98000

TOTAL_POLICY_AMOUNT
0 98000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"order": {
"Premium.total_premium": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_role_code"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH' AND {{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

What is the total amount of premiums that
a policy holder has paid by policy number?

1700618708.784875

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many policies does each
policy holder have by policy holder id?

1700618743.9116342

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
INNER JOIN Policy
ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

POLICY_HOLDER_ID NUMBER_OF_POLICIES
0 1 2
1 2 2

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier",
"Agreement_Party_Role.party_role_code"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"groupBy": {
"name": "policy__party_identifier_dim"
}
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"groupBy": {
"name": "policy__party_identifier_dim"
}
}
}

How many claims have
been placed by policy number?

1700618775.218219

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) as Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

{
"measures": [
"Claim.count_of_claims"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the total amount of
premiums paid by policy number?

1700618803.825976

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums_Paid
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many
policies do we have?

1700618838.05909

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

{
"measures": [
"Policy.count_of_policies"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": []
}

How many
claims do we have?

1700618864.4839451

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim

select * from {{
semantic_layer.query(metrics=['claims'])
}}

{
"measures": [
"Claim.count_of_claims"
],
"total": true,
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700618891.8719518

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier, SUM(Loss_Payment.Claim_Amount + Loss_Reserve.Claim_Amount) AS Total_Loss_Amounts
FROM Claim
LEFT JOIN Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier AND Loss_Payment.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Payment)
LEFT JOIN Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier AND Loss_Reserve.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Reserve)
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNTS
0 1 2200
1 2 4400

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT
0 12312701 1200 1000
1 12312702 2300 2100

{
"measures": [
"Loss_Payment.total_loss_payment"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
},
{
"measures": [
"Loss_Reserve.total_loss_reserve"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
}

Correct (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
]
}

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
]
}

Correct (Calculated)

What is the average time to
settle a claim by policy number?

1700618930.6096716

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT P.Policy_Number, AVG(DATEDIFF(day, C.Claim_Open_Date, C.Claim_Close_Date)) AS Average_Settlement_Days
FROM Claim C
JOIN Policy_Coverage_Detail PCD ON C.Claim_Identifier = PCD.Policy_Coverage_Detail_Identifier
JOIN Policy P ON PCD.Policy_Identifier = P.Policy_Identifier
WHERE C.Claim_Close_Date IS NOT NULL
GROUP BY P.Policy_Number

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_DAYS
0 31003000336 20.500000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

{
"measures": [
"Claim.average_time_to_settle_claim"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700618965.6878574

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium/Policy_Count) as Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) as Total_Premium, COUNT(DISTINCT Policy_Identifier) as Policy_Count
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) as SubQuery

select avg(total_policy_amount/number_of_policies) as average_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVERAGE_POLICY_SIZE
0 2849000.000000000000

{
"measures": [
"Policy.count_of_policies",
"Premium.total_premium"
],
"responseFormat": "default",
"dimensions": []
}

Correct (Calculated)

How many policies have
agents sold by agent id?

1700618998.3919916

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Agreement_Party_Role.Party_Identifier AS Agent_ID, COUNT(DISTINCT Policy.Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Agreement_Party_Role.Party_Role_Code = 'Agent'
GROUP BY Agreement_Party_Role.Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

Empty DataFrame
Columns: [AGENT_ID, POLICIES_SOLD]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"AG"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
}
}

What is the total amount of
premiums that a policy holder has paid?

1700619034.9982383

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'Policy Holder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"order": {
"Premium.total_premium": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_role_code"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH' AND {{ Dimension('policy_amount__has_premium') }} = '1'"
}
}

What is the total amount of premiums that
a policy holder has paid by policy number?

1700619068.1994417

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
INNER JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
INNER JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many policies does each
policy holder have by policy holder id?

1700619104.4000127

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
INNER JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Party_Role_Code = 'PolicyHolder'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

Empty DataFrame
Columns: [POLICY_HOLDER_ID, NUMBER_OF_POLICIES]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

How many claims have
been placed by policy number?

1700619139.1197217

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) as Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

{
"measures": [
"Claim.count_of_claims"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the total amount of
premiums paid by policy number?

1700619166.5052974

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium_Amount) as Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many
policies do we have?

1700619200.9352891

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

{
"measures": [
"Policy.count_of_policies"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": []
}

How many
claims do we have?

1700619227.7860324

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim

select * from {{
semantic_layer.query(metrics=['claims'])
}}

{
"measures": [
"Claim.count_of_claims"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700619254.206987

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT
Claim.Claim_Identifier,
SUM(Loss_Payment.Claim_Amount) AS Total_Loss_Payment,
SUM(Loss_Reserve.Claim_Amount) AS Total_Loss_Reserve,
(SUM(Loss_Payment.Claim_Amount) + SUM(Loss_Reserve.Claim_Amount)) AS Total_Loss_Amounts
FROM
Claim
LEFT JOIN
Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier
AND Loss_Payment.Amount_Type_Code = 'Loss Payment'
LEFT JOIN
Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier
AND Loss_Reserve.Amount_Type_Code = 'Loss Reserve'
GROUP BY
Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount', 'total_loss_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_PAYMENT TOTAL_LOSS_RESERVE TOTAL_LOSS_AMOUNTS
0 1 NaN NaN NaN
1 2 NaN NaN NaN

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT TOTAL_LOSS_AMOUNT
0 12312701 1200 1000 2200
1 12312702 2300 2100 4400

{
"measures": [
"Loss_Payment.total_loss_payment"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
},
{
"measures": [
"Loss_Reserve.total_loss_reserve"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
}

Correct (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
],
"orderBy": {
"groupBy": {
"name": "claim_identifier__company_claim_number"
}
}
}

True (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
]
}

Correct (Calculated)

What is the average time to
settle a claim by policy number?

1700619297.6245348

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT
Policy.Policy_Number,
AVG(DATEDIFF(day, Claim.Claim_Open_Date, Claim.Claim_Close_Date)) as Average_Settlement_Days
FROM
Claim
JOIN
Claim_Coverage ON Claim.Claim_Identifier = Claim_Coverage.Claim_Identifier
JOIN
Policy_Coverage_Detail ON Claim_Coverage.Policy_Coverage_Detail_Identifier = Policy_Coverage_Detail.Policy_Coverage_Detail_Identifier
JOIN
Policy ON Policy_Coverage_Detail.Policy_Identifier = Policy.Policy_Identifier
WHERE
Claim.Claim_Close_Date IS NOT NULL
GROUP BY
Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_DAYS
0 31003000336 20.500000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

{
"measures": [
"Claim.average_time_to_settle_claim"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700619330.5288408

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Policy_Count) as Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) as Total_Premium, COUNT(DISTINCT Policy_Identifier) as Policy_Count
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) as SubQuery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVG_POLICY_SIZE
0 2849000.000000000000

{
"measures": [
"Policy.count_of_policies",
"Premium.total_premium"
],
"responseFormat": "default",
"dimensions": []
}

Correct (Calculated)

How many policies have
agents sold by agent id?

1700619366.4777935

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'")
}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 43\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"AG"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

What is the total amount of
premiums that a policy holder has paid?

1700619398.2279983

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'Policy Holder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"order": {
"Premium.total_premium": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_role_code"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH' AND {{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

What is the total amount of premiums that
a policy holder has paid by policy number?

1700619433.8935506

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
INNER JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
INNER JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"groupBy": {
"name": "policy__policy_number"
}
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many policies does each
policy holder have by policy holder id?

1700619469.0301216

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Party_Role_Code = 'PolicyHolder'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

Empty DataFrame
Columns: [POLICY_HOLDER_ID, NUMBER_OF_POLICIES]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

How many claims have
been placed by policy number?

1700619501.4057007

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) as Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

{
"measures": [
"Claim.count_of_claims"
],
"dimensions": [
"Policy.policy_number"
],
"responseFormat": "default"
}

What is the total amount of
premiums paid by policy number?

1700619530.7067678

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium_Amount) AS Total_Premiums
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many
policies do we have?

1700619563.1345372

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

{
"measures": [
"Policy.count_of_policies"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": []
}

How many
claims do we have?

1700619589.697466

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim

select * from {{
semantic_layer.query(metrics=['claims'])
}}

{
"measures": [
"Claim.count_of_claims"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "claims"
}
]
}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700619617.037074

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier, SUM(Loss_Payment.Claim_Amount + Loss_Reserve.Claim_Amount) as Total_Loss_Amounts
FROM Claim
LEFT JOIN Claim_Amount as Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier AND Loss_Payment.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Payment)
LEFT JOIN Claim_Amount as Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier AND Loss_Reserve.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Reserve)
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['total_loss_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNTS
0 1 2200
1 2 4400

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER TOTAL_LOSS_AMOUNT
0 12312701 2200
1 12312702 4400

{
"measures": [
"Loss_Payment.total_loss_payment",
"Loss_Reserve.total_loss_reserve"
],
"dimensions": [
"Claim.company_claim_number"
],
"total": true,
"responseFormat": "default"
}

Correct (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
],
"orderBy": {
"metric": {
"name": "loss_payment_amount"
},
"descending": true
}
}

True (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
]
}

What is the average time to
settle a claim by policy number?

1700619656.5121171

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT Policy_Number, AVG(DATEDIFF(day, Claim_Open_Date, Claim_Close_Date)) as Average_Settlement_Days
FROM Claim
JOIN Policy ON Claim.Insurable_Object_Identifier = Policy.Policy_Identifier
WHERE Claim_Close_Date IS NOT NULL
GROUP BY Policy_Number

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_DAYS
0 31003000336 16.000000
1 31003000337 25.000000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

{
"measures": [
"Claim.average_time_to_settle_claim"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700619689.1199005

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium/Policy_Count) as Average_Policy_Size
FROM
(
SELECT SUM(Premium.Policy_Amount) as Total_Premium, COUNT(DISTINCT Policy.Policy_Identifier) as Policy_Count
FROM Premium
INNER JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
INNER JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
) as SubQuery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 4 at position 15\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

AVG_POLICY_SIZE
0 2849000.000000000000

{
"measures": [
"Policy.count_of_policies",
"Premium.total_premium"
],
"responseFormat": "default",
"dimensions": []
}

Correct (Calculated)

How many policies have
agents sold by agent id?

1700619724.8851383

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 43\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"AG"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

What is the total amount of
premiums that a policy holder has paid?

1700619757.1012866

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
INNER JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
INNER JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'Policy Holder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

{
"measures": [
"Premium.total_premium"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"total": true,
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_role_code"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH' AND {{ Dimension('policy_amount__has_premium') }} = '1'"
}
}

What is the total amount of premiums that
a policy holder has paid by policy number?

1700619790.520593

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT
Policy.Policy_Number,
SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM
Policy
INNER JOIN
Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
INNER JOIN
Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY
Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 3 at position 8\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} is not null"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many policies does each
policy holder have by policy holder id?

1700619825.5163927

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier, COUNT(Policy_Identifier) AS NumberOfPolicies
FROM Agreement_Party_Role
INNER JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Party_Role_Code = 'PolicyHolder'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

Empty DataFrame
Columns: [PARTY_IDENTIFIER, NUMBEROFPOLICIES]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

How many claims have
been placed by policy number?

1700619858.3004055

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS NumberOfClaims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBEROFCLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

{
"measures": [
"Claim.count_of_claims"
],
"dimensions": [
"Policy.policy_number"
],
"responseFormat": "default"
}

What is the total amount of
premiums paid by policy number?

1700619888.1262467

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"groupBy": {
"name": "policy__policy_number"
}
}
}

How many
policies do we have?

1700619921.7411366

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

{
"measures": [
"Policy.count_of_policies"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

{
"metrics": [
{
"name": "number_of_policies"
}
]
}

How many
claims do we have?

1700619948.0602055

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim;

select * from {{
semantic_layer.query(metrics=['claims'])
}}

{
"measures": [
"Claim.count_of_claims"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

{
"metrics": [
{
"name": "claims"
}
],
"groupBy": []
}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700619975.2855275

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT
Claim.Claim_Identifier,
SUM(Loss_Payment.Claim_Amount) as Loss_Payment_Amount,
SUM(Loss_Reserve.Claim_Amount) as Loss_Reserve_Amount,
(SUM(Loss_Payment.Claim_Amount) + SUM(Loss_Reserve.Claim_Amount)) as Total_Loss_Amount
FROM
Claim
LEFT JOIN
Claim_Amount as Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier
LEFT JOIN
Claim_Amount as Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier
GROUP BY
Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT TOTAL_LOSS_AMOUNT
0 1 18400 18400 36800
1 2 36000 36000 72000

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT
0 12312701 1200 1000
1 12312702 2300 2100

{
"measures": [
"Loss_Payment.total_loss_payment"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
},
{
"measures": [
"Loss_Reserve.total_loss_reserve"
],
"dimensions": [
"Claim.company_claim_number"
],
"order": {
"Claim.company_claim_number": "asc"
},
"responseFormat": "default"
}

Correct (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
]
}

True (Calculated)

{
"metrics": [
{
"name": "loss_payment_amount"
},
{
"name": "loss_reserve_amount"
}
],
"groupBy": [
{
"name": "claim_identifier__company_claim_number"
}
],
"orderBy": {
"metric": {
"name": "loss_payment_amount"
},
"descending": true
}
}

Correct (Calculated)

What is the average time to
settle a claim by policy number?

1700620015.8826213

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT
Policy.Policy_Number,
AVG(DATEDIFF(day, Claim.Claim_Open_Date, Claim.Claim_Close_Date)) AS Average_Settlement_Time
FROM
Claim
INNER JOIN
Claim_Coverage ON Claim.Claim_Identifier = Claim_Coverage.Claim_Identifier
INNER JOIN
Policy_Coverage_Detail ON Claim_Coverage.Policy_Coverage_Detail_Identifier = Policy_Coverage_Detail.Policy_Coverage_Detail_Identifier
INNER JOIN
Policy ON Policy_Coverage_Detail.Policy_Identifier = Policy.Policy_Identifier
WHERE
Claim.Claim_Open_Date IS NOT NULL AND
Claim.Claim_Close_Date IS NOT NULL
GROUP BY
Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_TIME
0 31003000336 20.500000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

{
"measures": [
"Claim.average_time_to_settle_claim"
],
"dimensions": [
"Policy.policy_number"
],
"responseFormat": "default"
}

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700620053.0689833

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Policy_Count) AS Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) AS Total_Premium, COUNT(DISTINCT Policy_Identifier) AS Policy_Count
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) AS Subquery

select * from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'],
compile=True)
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

SQL
0 SELECT \n MAX(subq_1211.number_of_policies) AS number_of_policies \n , MAX(subq_1216.total_policy_amount) AS total_policy_amount \n FROM ( \n SELECT \n SUM(1) AS number_of_policies \n FROM Analytics.dbt_cdw_benchmark.policy policy_src_463 \n ) subq_1211 \n CROSS JOIN ( \n SELECT \n SUM(Policy_Amount) AS total_policy_amount \n FROM Analytics.dbt_cdw_benchmark.policy_amount policy_amount_src_464 \n ) subq_1216

{
"measures": [
"Policy.count_of_policies",
"Premium.total_premium"
],
"responseFormat": "default",
"dimensions": []
}

Correct (Calculated)

How many policies have
agents sold by agent id?

1700620087.685413

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(DISTINCT Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 52\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"AG"
]
}
],
"order": {
"Policy.count_of_policies": "desc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"groupBy": {
"name": "policy__party_identifier_dim"
}
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'AG'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

What is the total amount of
premiums that a policy holder has paid?

1700620120.8104374

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
INNER JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
INNER JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'PolicyHolder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

{
"measures": [
"Premium.total_premium"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"total": true,
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
},
"limit": 100
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__party_role_code"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH' AND {{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

What is the total amount of premiums that
a policy holder has paid by policy number?

1700620155.8084154

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = '1'"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
},
{
"name": "policy_amount__has_premium"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many policies does each
policy holder have by policy holder id?

1700620190.1994724

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Agreement_Party_Role.Party_Identifier AS Policy_Holder_ID, COUNT(DISTINCT Policy.Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Agreement_Party_Role.Party_Role_Code = 'PolicyHolder'
GROUP BY Agreement_Party_Role.Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

Empty DataFrame
Columns: [POLICY_HOLDER_ID, NUMBER_OF_POLICIES]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

{
"measures": [
"Policy.count_of_policies"
],
"dimensions": [
"Agreement_Party_Role.party_identifier",
"Agreement_Party_Role.party_role_code"
],
"filters": [
{
"member": "Agreement_Party_Role.party_role_code",
"operator": "equals",
"values": [
"PH"
]
}
],
"total": true,
"responseFormat": "default"
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"groupBy": {
"name": "policy__party_identifier_dim"
}
}
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [
{
"name": "policy__party_identifier_dim"
}
],
"where": {
"sql": "{{ Dimension('policy__party_role_code') }} = 'PH'"
},
"orderBy": {
"metric": {
"name": "number_of_policies"
},
"descending": true
}
}

How many claims have
been placed by policy number?

1700620224.724538

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS Number_Of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

{
"measures": [
"Claim.count_of_claims"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

What is the total amount of
premiums paid by policy number?

1700620256.6324928

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy_Number, SUM(Policy_Amount) as Total_Premiums
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

POLICY_NUMBER TOTAL_PREMIUMS
0 31003000336 86000
1 31003000337 12000

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

{
"measures": [
"Premium.total_premium"
],
"dimensions": [
"Policy.policy_number"
],
"order": {
"Policy.policy_number": "asc"
},
"responseFormat": "default"
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

{
"metrics": [
{
"name": "total_policy_amount"
}
],
"groupBy": [
{
"name": "policy__policy_number"
}
],
"where": {
"sql": "{{ Dimension('policy_amount__has_premium') }} = 1"
},
"orderBy": {
"metric": {
"name": "total_policy_amount"
},
"descending": true
}
}

How many
policies do we have?

1700620291.429838

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

{
"measures": [
"Policy.count_of_policies"
],
"responseFormat": "default",
"dimensions": []
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": [],
"limit": null,
"where": null,
"orderBy": null
}

{
"metrics": [
{
"name": "number_of_policies"
}
],
"groupBy": []
}

How many
claims do we have?

1700620796.8538525

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim

select * from {{
semantic_layer.query(metrics=['claims'])
}}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700620834.6177843

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier, SUM(Loss_Payment.Claim_Amount + Loss_Reserve.Claim_Amount) AS Total_Loss_Amounts
FROM Claim
LEFT JOIN Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier AND Loss_Payment.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Payment)
LEFT JOIN Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier AND Loss_Reserve.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Reserve)
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNTS
0 1 2200
1 2 4400

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT
0 12312701 1200 1000
1 12312702 2300 2100

What is the average time to
settle a claim by policy number?

1700620871.8095293

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT
Policy_Number,
AVG(DATEDIFF(day, Claim_Open_Date, Claim_Close_Date)) AS Average_Settlement_Time
FROM
Claim
INNER JOIN
Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
WHERE
Claim_Open_Date IS NOT NULL AND Claim_Close_Date IS NOT NULL
GROUP BY
Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_TIME
0 31003000336 16.000000
1 31003000337 25.000000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700620905.0177634

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Number_Of_Policies) AS Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) AS Total_Premium, COUNT(DISTINCT Policy_Identifier) AS Number_Of_Policies
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) AS SubQuery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVG_POLICY_SIZE
0 2849000.000000000000

How many policies have
agents sold by agent id?

1700620937.935291

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(DISTINCT Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 52\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

What is the total amount of
premiums that a policy holder has paid?

1700620970.3819747

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Policy_Amount
INNER JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'PolicyHolder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

What is the total amount of premiums that
a policy holder has paid by policy number?

1700621006.2930784

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

How many policies does each
policy holder have by policy holder id?

1700621042.1665175

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 51\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

How many claims have
been placed by policy number?

1700621076.8352475

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

What is the total amount of
premiums paid by policy number?

1700621105.916554

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

How many
policies do we have?

1700621144.5095308

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

How many
claims do we have?

1700621170.8151052

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim;

select * from {{
semantic_layer.query(metrics=['claims'])
}}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700621198.624714

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier,
SUM(Loss_Payment.Claim_Amount + Loss_Reserve.Claim_Amount) AS Total_Loss_Amount
FROM Claim
LEFT JOIN Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier
AND Loss_Payment.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Payment)
LEFT JOIN Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier
AND Loss_Reserve.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Reserve)
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount', 'total_loss_amount'],
group_by='claim_identifier__company_claim_number')
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNT
0 1 2200
1 2 4400

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT TOTAL_LOSS_AMOUNT
0 12312701 1200 1000 2200
1 12312702 2300 2100 4400

What is the average time to
settle a claim by policy number?

1700621244.9217222

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT
Policy.Policy_Number,
AVG(DATEDIFF(day, Claim.Claim_Open_Date, Claim.Claim_Close_Date)) AS Average_Settlement_Days
FROM
Claim
JOIN
Policy_Coverage_Detail ON Claim.Insurable_Object_Identifier = Policy_Coverage_Detail.Insurable_Object_Identifier
JOIN
Policy ON Policy_Coverage_Detail.Policy_Identifier = Policy.Policy_Identifier
WHERE
Claim.Claim_Close_Date IS NOT NULL
GROUP BY
Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_DAYS
0 31003000336 16.000000
1 31003000337 25.000000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700621280.7997353

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Number_of_Policies) as Average_Policy_Size
FROM
(
SELECT SUM(Premium.Policy_Amount) as Total_Premium, COUNT(DISTINCT Policy.Policy_Identifier) as Number_of_Policies
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
) as Subquery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 4 at position 15\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

AVG_POLICY_SIZE
0 2849000.000000000000

How many policies have
agents sold by agent id?

1700621319.6523604

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 43\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

What is the total amount of
premiums that a policy holder has paid?

1700621349.3880537

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Policy_Amount
INNER JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Agreement_Party_Role.Party_Role_Code = 'PolicyHolder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

What is the total amount of premiums that
a policy holder has paid by policy number?

1700621384.976558

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
INNER JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
INNER JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

How many policies does each
policy holder have by policy holder id?

1700621422.421304

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
INNER JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

POLICY_HOLDER_ID NUMBER_OF_POLICIES
0 1 2
1 2 2

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

How many claims have
been placed by policy number?

1700621457.0774384

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS NumberOfClaims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBEROFCLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

What is the total amount of
premiums paid by policy number?

1700621489.4371703

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

How many
policies do we have?

1700621526.3308334

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

How many
claims do we have?

1700621554.9688256

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim;

select * from {{
semantic_layer.query(metrics=['claims'])
}}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700621584.3270102

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT
Claim.Claim_Identifier,
SUM(Loss_Payment.Claim_Amount) AS Total_Loss_Payment,
SUM(Loss_Reserve.Claim_Amount) AS Total_Loss_Reserve,
(SUM(Loss_Payment.Claim_Amount) + SUM(Loss_Reserve.Claim_Amount)) AS Total_Loss_Amount
FROM
Claim
LEFT JOIN
Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier
LEFT JOIN
Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier
GROUP BY
Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['total_loss_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_PAYMENT TOTAL_LOSS_RESERVE TOTAL_LOSS_AMOUNT
0 1 18400 18400 36800
1 2 36000 36000 72000

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER TOTAL_LOSS_AMOUNT
0 12312701 2200
1 12312702 4400

What is the average time to
settle a claim by policy number?

1700621625.7237844

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT Policy_Number, AVG(DATEDIFF(day, Claim_Open_Date, Claim_Close_Date)) as Average_Settlement_Time
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
WHERE Claim_Status_Code = 'CLOSED'
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

Empty DataFrame
Columns: [POLICY_NUMBER, AVERAGE_SETTLEMENT_TIME]
Index: []

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700621658.5766232

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium/Policy_Count) AS Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) AS Total_Premium, COUNT(DISTINCT Policy_Identifier) AS Policy_Count
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) AS SubQuery

select avg(total_policy_amount / number_of_policies) as avg_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVG_POLICY_SIZE
0 2849000.000000000000

How many policies have
agents sold by agent id?

1700621695.6867638

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'"
)}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 43\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

What is the total amount of
premiums that a policy holder has paid?

1700621730.4004533

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
JOIN Agreement_Party_Role ON Policy_Amount.Policy_Identifier = Agreement_Party_Role.Agreement_Identifier
WHERE Party_Role_Code = 'PolicyHolder';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

What is the total amount of premiums that
a policy holder has paid by policy number?

1700621771.8277347

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
INNER JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
INNER JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

How many policies does each
policy holder have by policy holder id?

1700621809.694619

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier, COUNT(Policy_Identifier) as NumberOfPolicies
FROM Agreement_Party_Role
INNER JOIN Policy ON Agreement_Party_Role.Agreement_Identifier = Policy.Policy_Identifier
WHERE Party_Role_Code = 'PolicyHolder'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

Empty DataFrame
Columns: [PARTY_IDENTIFIER, NUMBEROFPOLICIES]
Index: []

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

How many claims have
been placed by policy number?

1700621843.5726938

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

What is the total amount of
premiums paid by policy number?

1700621873.6030688

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) as Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

How many
policies do we have?

1700621909.3063946

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy;

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2

How many
claims do we have?

1700621937.1071858

SELECT COUNT(*) AS NoOfClaims
FROM claim

SELECT COUNT(*) FROM Claim;

select * from {{
semantic_layer.query(metrics=['claims'])
}}

What is the total loss amounts, which is the sum
of loss payment, loss reserve amount by claim number?

1700621964.1223974

SELECT
company_claim_number,
(ca_lp.claim_amount + ca_lr.claim_amount ) as LossAmount
FROM
Claim
inner join claim_amount ca_lp on claim.claim_identifier = ca_lp.claim_identifier
inner JOIN loss_payment ON ca_lp.claim_amount_identifier = loss_payment.claim_amount_identifier
inner join claim_amount ca_lr on claim.claim_identifier = ca_lr.claim_identifier
inner JOIN loss_reserve ON ca_lr.claim_amount_identifier = loss_reserve.claim_amount_identifier

SELECT Claim.Claim_Identifier, SUM(Loss_Payment.Claim_Amount) + SUM(Loss_Reserve.Claim_Amount) AS Total_Loss_Amounts
FROM Claim
LEFT JOIN Claim_Amount AS Loss_Payment ON Claim.Claim_Identifier = Loss_Payment.Claim_Identifier AND Loss_Payment.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Payment)
LEFT JOIN Claim_Amount AS Loss_Reserve ON Claim.Claim_Identifier = Loss_Reserve.Claim_Identifier AND Loss_Reserve.Claim_Amount_Identifier IN (SELECT Claim_Amount_Identifier FROM Loss_Reserve)
GROUP BY Claim.Claim_Identifier;

select * from {{
semantic_layer.query(metrics=['loss_payment_amount', 'loss_reserve_amount', 'total_loss_amount'],
group_by=['claim_identifier__company_claim_number'])
}}

COMPANY_CLAIM_NUMBER LOSSAMOUNT
0 12312701 2200
1 12312702 4400

CLAIM_IDENTIFIER TOTAL_LOSS_AMOUNTS
0 1 2200
1 2 4400

CLAIM_IDENTIFIER__COMPANY_CLAIM_NUMBER LOSS_PAYMENT_AMOUNT LOSS_RESERVE_AMOUNT TOTAL_LOSS_AMOUNT
0 12312701 1200 1000 2200
1 12312702 2300 2100 4400

What is the average time to
settle a claim by policy number?

1700622011.793798

select policy_number, avg(DATE_DIFF(claim_open_date, claim_close_date, "day"))
from claim
inner join claim_coverage on claim.claim_identifier = claim_coverage.claim_identifier
inner join policy_coverage_detail on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
where claim_close_date IS NOT NULL
group by policy_number

SELECT Policy.Policy_Number, AVG(DATEDIFF(day, Claim.Claim_Open_Date, Claim.Claim_Close_Date)) as Average_Settlement_Days
FROM Claim
JOIN Policy_Coverage_Detail ON Claim.Claim_Identifier = Policy_Coverage_Detail.Policy_Coverage_Detail_Identifier
JOIN Policy ON Policy_Coverage_Detail.Policy_Identifier = Policy.Policy_Identifier
WHERE Claim.Claim_Close_Date IS NOT NULL
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['avg_time_to_settle_claim'],
group_by=['policy__policy_number'])
}}

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 71\ninvalid identifier '"day"' (InvalidArgument; Prepare)

POLICY_NUMBER AVERAGE_SETTLEMENT_DAYS
0 31003000336 20.500000

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['policy__policy_number'] cannot be resolved for metrics ['avg_time_to_settle_claim']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'policy__policy_number':\n [] (InvalidArgument; Prepare)

What is the average policy size which is the the
total amount of premium divided by the number of policies?

1700622046.2120285

SELECT
SUM(policy_amount)/COUNT(DISTINCT policy_number) AS avgPolicySize
FROM
policy_coverage_detail
inner join policy on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join policy_amount on policy_coverage_detail.policy_coverage_detail_identifier = policy_amount.policy_coverage_detail_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier

SELECT AVG(Total_Premium / Number_Of_Policies) AS Average_Policy_Size
FROM
(
SELECT SUM(Policy_Amount) AS Total_Premium, COUNT(DISTINCT Policy_Identifier) AS Number_Of_Policies
FROM Policy_Amount
INNER JOIN Premium
ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
) AS Subquery

select avg(total_policy_amount / number_of_policies) as average_policy_size from {{
semantic_layer.query(metrics=['total_policy_amount', 'number_of_policies'])
}}

AVERAGE_POLICY_SIZE
0 49000.000000000000

AVERAGE_POLICY_SIZE
0 2849000.000000000000

How many policies have
agents sold by agent id?

1700622085.724862

select party_identifier as agentid, count(policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'AG'
group by party_identifier

SELECT Party_Identifier AS Agent_ID, COUNT(Policy_Identifier) AS Policies_Sold
FROM Agreement_Party_Role
WHERE Party_Role_Code = 'Agent'
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'AG'")
}}

AGENTID NOOFPOLICIES
0 2 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 43\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 2 2

What is the total amount of
premiums that a policy holder has paid?

1700622118.1096196

select party_identifier, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT SUM(Policy_Amount) AS Total_Premiums_Paid
FROM Premium
INNER JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
WHERE Amount_Type_Code = 'PREM';

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'")
}}

PARTY_IDENTIFIER SUM(POLICY_AMOUNT)
0 1 98000

TOTAL_PREMIUMS_PAID
0 NaN

TOTAL_POLICY_AMOUNT
0 98000

What is the total amount of premiums that
a policy holder has paid by policy number?

1700622154.9073586

select party_identifier, policy_number, sum(policy_amount)
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier, policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Policy
JOIN Policy_Amount ON Policy.Policy_Identifier = Policy_Amount.Policy_Identifier
JOIN Premium ON Policy_Amount.Policy_Amount_Identifier = Premium.Policy_Amount_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number', 'policy__party_identifier_dim'],
where="{{ Dimension('policy_amount__has_premium') }} = 1 AND {{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER POLICY_NUMBER SUM(POLICY_AMOUNT)
0 1 31003000336 86000
1 1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 1 31003000336 86000
1 1 31003000337 12000

How many policies does each
policy holder have by policy holder id?

1700622192.5717607

select party_identifier, count( policy.policy_number) as NoOfPolicies
from agreement_party_role
join policy on agreement_party_role.agreement_identifier = policy.policy_identifier
where agreement_party_role.party_role_code = 'PH'
group by party_identifier

SELECT Party_Identifier AS Policy_Holder_ID, COUNT(Policy_Identifier) AS Number_of_Policies
FROM Agreement_Party_Role
GROUP BY Party_Identifier;

select * from {{
semantic_layer.query(metrics=['number_of_policies'],
group_by=['policy__party_identifier_dim'],
where="{{ Dimension('policy__party_role_code') }} = 'PH'"
)}}

PARTY_IDENTIFIER NOOFPOLICIES
0 1 2

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 51\ninvalid identifier 'POLICY_IDENTIFIER' (InvalidArgument; Prepare)

POLICY__PARTY_IDENTIFIER_DIM NUMBER_OF_POLICIES
0 1 2

How many claims have
been placed by policy number?

1700622226.500634

select policy_number, count(company_claim_number) as NoOfClaims
from
policy
inner join policy_coverage_detail on policy.policy_identifier = policy_coverage_detail.policy_identifier
inner join claim_coverage on claim_coverage.policy_coverage_detail_identifier = policy_coverage_detail.policy_coverage_detail_identifier
inner join claim on claim.claim_identifier = claim_coverage.claim_identifier
group by policy_number

SELECT Policy_Number, COUNT(*) AS Number_of_Claims
FROM Claim
JOIN Policy ON Claim.Claim_Identifier = Policy.Policy_Identifier
GROUP BY Policy_Number;

select * from {{
semantic_layer.query(metrics=['claims'],
group_by=['claim_identifier__policy_number'])
}}

POLICY_NUMBER NOOFCLAIMS
0 31003000336 2

POLICY_NUMBER NUMBER_OF_CLAIMS
0 31003000336 1
1 31003000337 1

error
0 INVALID_ARGUMENT: [FlightSQL] Unable To Satisfy Query Error: Dimensions ['claim_identifier__policy_number'] cannot be resolved for metrics ['claims']. The invalid dimension may not exist, require an ambiguous join (e.g. a join path that can be satisfied in multiple ways), or require a fanout join.\n\nSuggestions for invalid dimension 'claim_identifier__policy_number':\n ['claim_identifier__company_claim_number',\n 'claim_identifier__company_subclaim_number',\n 'claim_identifier__policy_coverage_detail',\n 'claim_identifier__claim_identifier',\n 'claim_identifier__claim_description',\n 'claim_identifier__claim_open_date__year'] (InvalidArgument; Prepare)

What is the total amount of
premiums paid by policy number?

1700622256.5929813

select policy_number, sum(policy_amount) as TotalPremiumAmount
from
policy
inner join policy_amount on policy.policy_identifier = policy_amount.policy_identifier
inner join premium on premium.policy_amount_identifier = policy_amount.policy_amount_identifier
group by policy_number

SELECT Policy.Policy_Number, SUM(Premium.Policy_Amount) AS Total_Premiums_Paid
FROM Premium
INNER JOIN Policy_Amount ON Premium.Policy_Amount_Identifier = Policy_Amount.Policy_Amount_Identifier
INNER JOIN Policy ON Policy_Amount.Policy_Identifier = Policy.Policy_Identifier
GROUP BY Policy.Policy_Number;

select * from {{
semantic_layer.query(metrics=['total_policy_amount'],
group_by=['policy__policy_number'],
where="{{ Dimension('policy_amount__has_premium') }} = 1"
)}}

POLICY_NUMBER TOTALPREMIUMAMOUNT
0 31003000336 86000
1 31003000337 12000

error
0 INVALID_ARGUMENT: [FlightSQL] SQL compilation error: error line 1 at position 33\ninvalid identifier 'PREMIUM.POLICY_AMOUNT' (InvalidArgument; Prepare)

POLICY__POLICY_NUMBER TOTAL_POLICY_AMOUNT
0 31003000336 86000
1 31003000337 12000

How many
policies do we have?

1700622293.7305334

SELECT COUNT(*) AS NoOfPolicy
FROM policy

SELECT COUNT(*) FROM Policy;

select * from {{
semantic_layer.query(metrics=['number_of_policies'])
}}

NUMBER_OF_POLICIES
0 2