Sailthru

Sample Queries

1. How to query items for all the purchases?

   SELECT * FROM <sailthru_schema>.purchase
   INNER JOIN <sailthru_schema>.purchase_item
   ON <sailthru_schema>.purchase._fivetran_purchase_id=<sailthru_schema>.purchase_item._fivetran_purchase_id
   

2. How to query for all the purchases from a campaign?

   SELECT * FROM <sailthru_schema>.purchase INNER JOIN <sailthru_schema>.purchase_item
   ON <sailthru_schema>.purchase._fivetran_purchase_id=<sailthru_schema>.purchase_item._fivetran_purchase_id
   WHERE <sailthru_schema>.purchase.campaign_id='<campaign_id>'
   

3. How to query for all the purchases for a user?

   SELECT * FROM <sailthru_schema>.purchase INNER JOIN <sailthru_schema>.purchase_item
   ON <sailthru_schema>.purchase._fivetran_purchase_id=<sailthru_schema>.purchase_item._fivetran_purchase_id
   WHERE <sailthru_schema>.purchase.user_id='<user_id>'
   

4. How to calculate the total purchase price for a campaign?

   SELECT SUM(<sailthru_schema>.purchase_item.unit_price * <sailthru_schema>.purchase_item.quantity)
   FROM <sailthru_schema>.purchase INNER JOIN <sailthru_schema>.purchase_item
   ON <sailthru_schema>.purchase._fivetran_purchase_id=<sailthru_schema>purchase_item._fivetran_purchase_id
   WHERE <sailthru_schema>.purchase.campaign_id='<campaign_id>'
   

5. How to calculate the total purchase price of a user?

   SELECT SUM(<sailthru_schema>.purchase_item.unit_price * <sailthru_schema>.purchase_item.quantity)
   FROM <sailthru_schema>.purchase INNER JOIN <sailthru_schema>.purchase_item
   ON <sailthru_schema>.purchase._fivetran_purchase_id=<sailthru_schema>purchase_item._fivetran_purchase_id
   WHERE <sailthru_schema>.purchase.user_id='<user_id>'
   

6. How to calculate the total purchase price for a specific item?

   SELECT SUM(<sailthru_schema>.purchase_item.unit_price * <sailthru_schema>.purchase_item.quantity)
   FROM <sailthru_schema>.purchase_item
   WHERE <sailthru_schema>.purchase_item.item='<item>'
   

7. How to query for all the users who have made a purchase from a specific campaign?

   SELECT DISTINCT(<sailthru_schema>.purchase.user_id), ... FROM <sailthru_schema>.purchase INNER JOIN <sailthru_schema>.user
   ON <sailthru_schema>.purchase.user_id=<sailthru_schema>.user.user_id
   WHERE <sailthru_schema>.purchase.campaign_id='<campaign_id>'
   

8. How to query click details for all the campaigns?

   SELECT * FROM <sailthru_schema>.campaign_query
   INNER JOIN <sailthru_schema>.campaign_click
   ON <sailthru_schema>.campaign_query._fivetran_campaign_query_id=<sailthru_schema>.campaign_click._fivetran_campaign_query_id
   

9. How to query for all the clicks of a campaign?

   SELECT * FROM <sailthru_schema>.campaign_query INNER JOIN <sailthru_schema>.campaign_click
   ON <sailthru_schema>.campaign_query._fivetran_campaign_query_id=<sailthru_schema>.campaign_click._fivetran_campaign_query_id
   WHERE <sailthru_schema>.campaign_query.campaign_id='<campaign_id>'
   

10. How to query for all the clicks of a user?

    SELECT * FROM <sailthru_schema>.campaign_query INNER JOIN <sailthru_schema>.campaign_click
    ON <sailthru_schema>.campaign_query._fivetran_campaign_query_id=<sailthru_schema>.campaign_click._fivetran_campaign_query_id
    WHERE <sailthru_schema>.campaign_query.user_id='<user_id>'
    

11. How to query for all the users to whom a campaign was sent?

    SELECT DISTINCT(<sailthru_schema>.campaign_query.user_id), ... FROM <sailthru_schema>.campaign_query INNER JOIN <sailthru_schema>.user
    ON <sailthru_schema>.campaign_query.user_id=<sailthru_schema>.user.user_id
    WHERE <sailthru_schema>.campaign_query.campaign_id='<campaign_id>'