mkt_customers (customer_id, country) lists every marketing contact; country can be NULL. consent_answers (answer_id, customer_id, source, opted_in, answered_at) records each time a source (the CRM, the website, the app) asked a customer for email consent. opted_in is TRUE, FALSE, or NULL when the customer was asked but didn't answer.
Decide each customer's consent:
- •Only each source's latest answer counts (latest
answered_at; at the same time, the larger answer_id) — even when that latest answer is NULL. A re-ask nobody answered means the earlier answer from that source has lapsed. - •blocked — any counted answer is FALSE.
- •allowed — not blocked, and at least one counted answer is TRUE.
- •unknown — everything else: no counted answers at all, or only NULL ones.
Report, per country: the number of customers, how many are allowed, blocked and unknown, and allowed_pct (allowed as a percentage of the country's customers, rounded to 1 decimal place). Every customer counts, whether they were asked or not. Customers with no country form one row with country NULL.
Columns: country, customers, allowed, blocked, unknown, allowed_pct. Sort by country, with the NULL country last.