Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

Run Queries on Data

Query 1: Send Notifications

This query calls a function that matches offers with subscribers and sends notifications to subscribers.

SQL

USE martech;

INSERT INTO notifications (ts,city_id, subscriber_id, offer_id, cost_cents, lonlat)
SELECT now(),* FROM match_offers_to_subscribers("second");

Query 2: Analyze Conversion Rate

This query calculates the number of customers who have made purchases so far and determines the conversion rate.

Note

As time progresses, more users will have made purchases, and the conversion rate may change.

SQL

USE martech;

SELECT
    *,
    CONCAT(FORMAT((total_conversions / total_notifications) * 100,2),"%") AS conversion_rate,
RANK() OVER (ORDER BY ((total_conversions / total_notifications)) desc) AS rank
FROM (
    SELECT
        offer_notification.customer,offer_notification.notification_zone,
        COUNT(offer_notification.offer_id) as total_notifications,
        COUNT(purchases.vendor) as total_conversions
    FROM (
        SELECT offers.offer_id, offers.customer , notifications.ts, notifications.city_id, notifications.subscriber_id, offers.notification_zone
        FROM offers, notifications
        WHERE offers.offer_id = notifications.offer_id
    ) offer_notification
    LEFT JOIN purchases ON
        offer_notification.city_id = purchases.city_id
        AND offer_notification.subscriber_id = purchases.subscriber_id
        AND purchases.ts > offer_notification.ts
        AND purchases.vendor = offer_notification.customer
    GROUP BY 1
    LIMIT 5	/* remove LIMIT to see full result */
);

+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
| customer    | notification_zone                                                                                                                                                          | total_notifications | total_conversions | conversion_rate | rank |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
| Eayo        | POLYGON((-74.00250000 40.73750000, -74.00000000 40.73750000, -74.00000000 40.74000000, -74.00250000 40.74000000, -74.00250000 40.73750000))                          |                 104 |                46 | 44.23%          |    1 |
| Linklinks   | POLYGON((-73.99750000 40.74000000, -73.99500000 40.74000000, -73.99500000 40.74250000, -73.99750000 40.74250000, -73.99750000 40.74000000))                          |                  99 |                29 | 29.29%          |    2 |
| Eabox       | POLYGON((-73.99000000 40.74000000, -73.98750000 40.74000000, -73.98750000 40.74250000, -73.99000000 40.74250000, -73.99000000 40.74000000))                          |                 587 |               158 | 26.92%          |    3 |
| Oyoba       | POLYGON((-74.00500000 40.73500000, -74.00250000 40.73500000, -74.00250000 40.73750000, -74.00500000 40.73750000, -74.00500000 40.73500000))                          |                 273 |                73 | 26.74%          |    4 |
| Brightbean  | POLYGON((-73.98250000 40.72500000, -73.98000000 40.72500000, -73.98000000 40.72750000, -73.98250000 40.72750000, -73.98250000 40.72500000))                          |                 164 |                25 | 15.24%          |    5 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+