Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

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 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

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 |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+

Run Queries on Data

On this page

Query 1: Send Notifications

Query 2: Analyze Conversion Rate

Was this article helpful?

On this page

Was this article helpful?