Sailthru

Sample Queries

1. How to query items for all the purchases?

   SELECT * FROM .purchase
   INNER JOIN .purchase_item
   ON .purchase._fivetran_purchase_id=.purchase_item._fivetran_purchase_id
   

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

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

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

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

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

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

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

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

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

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

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

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

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

   SELECT * FROM .campaign_query
   INNER JOIN .campaign_click
   ON .campaign_query._fivetran_campaign_query_id=.campaign_click._fivetran_campaign_query_id
   

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

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

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

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

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

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