Sign inTry Free

Run Queries on Data

On this page

You can start running queries once the data is loaded into your SingleStore database. Here are some example queries to be used with the MarTech dataset.

Query 1: Send Notifications

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

USE martech;


INSERT INTO notifications (ts,city_id, subscriber_id, offer_id, cost_cents, lonlat)
SELECT now(),* FROM match_offers_to_subscribers("second");
Query OK, 5234 rows affected (2.166 s)

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.

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 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
| Jazzy    | POLYGON((-74.01000000 40.71250000, -74.00750000 40.71250000, -74.00750000 40.71500000, -74.01000000 40.71500000, -74.01000000 40.71250000)) |                  90 |                51 | 56.67%          |    1 |
| Eidel    | POLYGON((-74.00000000 40.74500000, -73.99750000 40.74500000, -73.99750000 40.74750000, -74.00000000 40.74750000, -74.00000000 40.74500000)) |                  78 |                34 | 43.59%          |    2 |
| Skibox   | POLYGON((-74.01500000 40.72500000, -74.01250000 40.72500000, -74.01250000 40.72750000, -74.01500000 40.72750000, -74.01500000 40.72500000)) |                  40 |                17 | 42.50%          |    3 |
| Kazu     | POLYGON((-73.99750000 40.72250000, -73.99500000 40.72250000, -73.99500000 40.72500000, -73.99750000 40.72500000, -73.99750000 40.72250000)) |                  88 |                24 | 27.27%          |    4 |
| Rhyloo   | POLYGON((-73.98250000 40.72250000, -73.98000000 40.72250000, -73.98000000 40.72500000, -73.98250000 40.72500000, -73.98250000 40.72250000)) |                 181 |                23 | 12.71%          |    5 |
+----------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------------+-------------------+-----------------+------+
5 rows in set (0.51 sec)

Last modified: October 10, 2024

Was this article helpful?

On this page

Was this article helpful?