Defog

SELECT

CUSTOMERS.CUSTOMER_NAME,

COUNT(CASE WHEN RENT_PAID_DATE <= RENT_DUE_DATE THEN 1 END) / COUNT(*) * 100 AS "Percentage of Rent Paid on Time"

FROM

APPFOLIO_TEST.PUBLIC.RENT_DUE

JOIN APPFOLIO_TEST.PUBLIC.CUSTOMERS ON RENT_DUE.CUSTOMER_ID = CUSTOMERS.CUSTOMER_ID

GROUP BY

CUSTOMERS.CUSTOMER_NAME;

SELECT c.customer_id, c.customer_name, c.contract_arr, SUM(e.notifications_received) AS notifications_received, SUM(e.notifications_opened) AS notifications_opened, SUM(e.notifications_replied) AS notifications_replied, (SUM(e.notifications_received) + SUM(e.notifications_opened) + SUM(e.notifications_replied)) / c.contract_arr AS value_for_money

FROM the-broadline.notification_events.events AS e

INNER JOIN the-broadline.notification_events.customers AS c

ON e.customer_id = c.customer_id

GROUP BY c.customer_id, c.customer_name, c.contract_arr

ORDER BY value_for_money DESC

;

SELECT c.customer_name, c.contract_arr, SUM(e.notifications_attempted) AS notifications_attempted, SUM(e.notifications_failed) AS notifications_failed, SUM(e.notifications_failed) / SUM(e.notifications_attempted) AS failure_rate

FROM the-broadline.notification_events.events AS e

INNER JOIN the-broadline.notification_events.customers AS c

ON e.customer_id = c.customer_id

WHERE DATE(e.hour) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)

GROUP BY c.customer_name, c.contract_arr

ORDER BY failure_rate DESC

;

SELECT c.customer_name, c.contract_arr, SUM(e.notifications_attempted) AS notifications_attempted, SUM(e.notifications_failed) AS notifications_failed, SUM(e.notifications_failed) / SUM(e.notifications_attempted) AS failure_rate

FROM the-broadline.notification_events.events AS e

INNER JOIN the-broadline.notification_events.customers AS c

ON e.customer_id = c.customer_id

WHERE DATE(e.hour) >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH)

GROUP BY c.customer_name, c.contract_arr

ORDER BY failure_rate DESC

;