Building Your First Report (Manual SQL)

8. Building Your First Report (Manual SQL)

8.1 What This Section Is For

This section is for experts who prefer direct SQL control and want predictable, reviewable report logic.

8.2 Recommended Build Sequence

Pick a base fact view in the sidebar.
Start narrow with LIMIT, a date filter, and explicit columns.
Validate row shape and baseline counts.
Add joins one at a time and re-validate.
Add calculations, sorting, and final output shaping.

8.3 Starter Example (Narrow First)

SELECT
revision_id,
policy_type_id,
inforce_premium
FROM m_inforce_policies
LIMIT 200;

Then add JOINs to resolve IDs into readable values, as shown in the next example.

8.4 Example: Policies in Force with Readable Fields

Join m_inforce_policies to dimension views to get policy number, policy type, and address instead of raw IDs:

-- One address per policy (for example, first alphabetically)
SELECT
t.state,
t.policy_type,
r.policy_number,
MIN(l.full_address) AS full_address
FROM m_inforce_policies i
JOIN v_policy_types t ON t.policy_type_id = i.policy_type_id
JOIN v_revisions r ON r.revision_id = i.revision_id
JOIN v_properties l ON l.revision_id = i.revision_id
GROUP BY t.state, t.policy_type, r.policy_number;

Use the left sidebar to inspect each view's fields, such as full_address, latitude, and longitude in v_properties, and add or swap columns as needed.

8.5 Why This Sequence Works

Incremental construction isolates errors early. If you build joins and calculations all at once, troubleshooting becomes expensive and ambiguous. If you build in layers, you can quickly identify where row inflation, null propagation, or unexpected filtering was introduced.

8.6 Expert Tips

Avoid SELECT * in final reports. Explicit columns are easier to review and safer over time.
Add comments for future maintainers:

-- Policy trend sample for monthly review

Validate one clause at a time during troubleshooting.