Common use cases of Data Query
Query examples
-
For customer account A00000977, retrieve:
- The account number
- The account name
- The account status
- The username of the Zuora user who created the account
SELECT a.accountnumber, a.name, a.status, u.username FROM Account a JOIN User u ON a.createdbyid = u.id WHERE a.accountnumber = 'A00000977'
accountnumber,name,status,username A00000977,West Corporation,Active,ksmith@exmaple.com
-
Retrieve all processed usage records, including the account number and account name associated with each usage record:
SELECT pu.id, pu.amount, a.accountnumber, a.name FROM ProcessedUsage pu JOIN Usage u ON pu.usageid = u.id JOIN Account a ON u.accountnumber = a.accountnumber
id,amount,accountnumber,name 2c92c0f8-6329-99a2-0163-52a457cf2b44,345.400000,A00000977,West Corporation 2c92c0f8-6329-99a2-0163-52a457cf2b44,147.580000,A00000464,East Enterprises 2c92c0f8-6329-99a2-0163-52a457cf2b44,197.190000,A00000985,North Services ...
-
Retrieve the parent account number of each customer account:
SELECT a.accountnumber, pa.accountnumber as parentnumber FROM Account a LEFT OUTER JOIN Account pa ON a.parentid = pa.id
accountnumber,parentnumber A00000674,A00000550 A00000650, A00000783,A00000886 ...
-
Retrieve all Order MRR metrics and Order TCB metrics:
SELECT 'MRR' as metric, OrderMrr.id, OrderMrr.value FROM OrderMrr UNION SELECT 'TCB' as metric, OrderTcb.id, OrderTcb.value FROM OrderTcb
metric,id,value MRR,2c92c0f8-6329-99a2-0163-8792e2ae736a,148.500000 MRR,2c92c0f8-6329-99a2-0163-805c8fb924be,350.000000 ... TCB,2c92c0f8-6329-99a2-0163-9a10f930a800,147.300000 TCB,2c92c0f8-6329-99a2-0163-8230c94c7c0b,-179.990000 ...
-
Retrieve the 3 products that became effective most recently:
SELECT p.id, p.name, p.effectivestartdate FROM Product p ORDER BY p.effectivestartdate DESC LIMIT 3
id,name,effectivestartdate 2c92c0f8-6680-fd09-0166-a424179630c0,Spring 19 Plan,2019-03-01 2c92c0f8-6680-fd09-0166-8187039d72f7,Student Plan,2018-07-01 2c92c0f8-6680-fd09-0166-75fc4e54a574,Spring 18 Plan,2018-03-01
-
Retrieve the 3 products that became effective most recently and are still effective:
SELECT p.id, p.name, p.effectivestartdate, p.effectiveenddate FROM Product p WHERE current_date <= p.effectiveenddate ORDER BY p.effectivestartdate DESC LIMIT 3
id,name,effectivestartdate,effectiveenddate 2c92c0f8-6680-fd09-0166-a424179630c0,Spring 19 Plan,2019-03-01,2019-05-31 2c92c0f8-6680-fd09-0166-8187039d72f7,Student Plan,2018-07-01,2023-06-30 2c92c0f8-6680-fd09-0166-07b3580a31d7,Full Access Plan,2017-01-01,2026-12-31
-
Retrieve the rate plans in a product, using a 32-character product identifier:
SELECT prp.id, prp.name FROM ProductRatePlan prp WHERE prp.productid = to_uuid36('2c92c0f86680fd090166a424179630c0')
id,name 2c92c0f8-6329-99a2-0163-c016fabe8ea8,Monthly Unlimited 2c92c0f8-6329-99a2-0163-b10b3b003e43,Monthly Capped ...
-
Retrieve the 32-character identifiers of the rate plans in a product:
SELECT to_uuid32(prp.id) AS id32 FROM ProductRatePlan prp WHERE prp.productid = to_uuid36('2c92c0f86680fd090166a424179630c0')
id32 2c92c0f8632999a20163c016fabe8ea8 2c92c0f8632999a20163b10b3b003e43 ...
-
Retrieve the number of subscriptions owned by each customer account:
SELECT a.accountnumber, count(s.id) as subs FROM Account a JOIN Subscription s ON a.id = s.accountid GROUP BY a.accountnumber
accountnumber,subs A00000977,2 A00000464,1 A00000985,4 ...
-
Retrieve the yearly average invoice amount for an account:
SELECT year(i.invoicedate) as year, avg(i.amount) as avg FROM Invoice i WHERE i.accountid = (SELECT a.id FROM Account a WHERE a.accountnumber = 'A00118294') GROUP BY year(i.invoicedate) ORDER BY year(i.invoicedate)
year,avg 2017,195.200000 2018,230.000000 2019,193.587000
- Retrieve all records in a Custom Object named Vehicle:
SELECT make__c, model__c, year__c, VIN__c FROM default__Vehicle
ZOQL to SQL conversions
See the following examples of converting ZOQL queries to SQL queries.
- Retrieve Account, Subscription, and Product information from RatePlanCharge
ZOQL SQL SELECT Id, Product.id, Subscription.Name, Product.Name, Account.CrmID, RatePlan.id, RatePlanCharge.ChargeNumber, Subscription.id, Account.id, ProductRatePlan.id FROM RatePlanCharge
SELECT RatePlanCharge.Id, RatePlanCharge.ChargeNumber, Subscription.Name, Product.Name, Account.CrmID FROM RatePlanCharge JOIN RatePlan ON RatePlan.id=RatePlanCharge.rateplanid JOIN Subscription ON RatePlan.SubscriptionId=Subscription.id JOIN Account ON Subscription.AccountId=Account.id JOIN ProductRatePlan ON RatePlan.ProductRatePlanId = ProductRatePlan.id JOIN Product ON Product.id=ProductRatePlan.productid
- Retrieve Account, Subscription, and Product Information from Invoice
ZOQL SQL SELECT Account.id, Invoice.id, ProductRatePlan.id, ProductRatePlanCharge.id, RatePlan.id, RatePlanCharge.id, Subscription.id FROM InvoiceItem
SELECT invoiceitem.accountid, invoice.id, productrateplan.id, productrateplancharge.id, rateplan.id, rateplancharge.id, subscription.id FROM invoiceitem JOIN productrateplan on invoiceitem.productrateplanid = productrateplan.id JOIN rateplancharge on invoiceitem.rateplanchargeid = rateplancharge.id JOIN productrateplancharge on invoiceitem.productrateplanchargeid = productrateplancharge.id JOIN rateplan on invoiceitem.rateplanid = rateplan.id JOIN account on invoiceitem.accountid = account.id JOIN invoice on invoiceitem.invoiceid = invoice.id JOIN subscription on invoiceitem.subscriptionid = subscription.id
- Retrieve all active Subscriptions with no Rate Plan
ZOQL SQL SELECT Subscription.id, count(RatePlanCharge.id) FROM RatePlanCharge WHERE Subscription.status = 'Active' GROUP BY Subscription.id HAVING count(RatePlanCharge.id) = 0
SELECT subscription.id, count(ratePlanCharge.id) FROM subscription LEFT JOIN ratePlan ON subscription.id = ratePlan.subscriptionId LEFT JOIN ratePlanCharge ON ratePlan.Id = ratePlanCharge.ratePlanId WHERE subscription.status = 'Active' GROUP BY subscription.id HAVING count(ratePlanCharge.id) = 0