> This page is for Docs.

> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://zuora.ferndocs.com/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://zuora.ferndocs.com/_mcp/server.

# Common use cases of data query

> Here are examples of common data query use cases, including retrieving account details, processed usage records, and product information.

Here are examples of common data query use cases, including retrieving account details, processed usage records, and product information.

## 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 invoices from a bill run that did not successfully generate a PDF. Check those with a status of Pending, Processing, or Error.

  ```
  SELECT 
      i.InvoiceNumber,
      i.Id,
      i.CreatedDate,
      i.PostedDate,
      i.Status, 
      ib.GenerationStatus, 
      ib.ErrorCategory, 
      ib.ErrorMessage
  FROM 
      invoice i
  LEFT JOIN 
      billingdocumentpdfgeneration ib 
      ON i.Id = ib.DocumentId
  WHERE 
      i.Status = 'Posted' 
      AND i.SourceId IN ('BR-00011439') 
      AND ib.GenerationStatus != 'Generated'
  ```
* Retrieve the current status of PDF generation requests for invoices from a bill run:

  ```
  SELECT 
      i.InvoiceNumber,
      i.Id,
      i.CreatedDate,
      i.PostedDate,
      i.Status, 
      ib.GenerationStatus, 
      ib.ErrorCategory, 
      ib.ErrorMessage
  FROM 
      invoice i
  LEFT JOIN 
      billingdocumentpdfgeneration ib 
      ON i.Id = ib.DocumentId
  WHERE 
      i.Status = 'Posted' 
      AND i.SourceId IN ('BR-00011439')
  ```
* 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

  <table>
    <tr>
      <th>
        ZOQL
      </th>

      <th>
        SQL
      </th>
    </tr>

    <tr>
      <td>
        ```
        SELECT Id, Product.id, Subscription.Name, 
        Product.Name, Account.CrmID, RatePlan.id, 
        RatePlanCharge.ChargeNumber, Subscription.id, 
        Account.id, ProductRatePlan.id 
        FROM RatePlanCharge
        ```
      </td>

      <td>
        ```
        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
        ```
      </td>
    </tr>
  </table>
* Retrieve Account, Subscription, and Product Information from Invoice

  <table>
    <tr>
      <th>
        ZOQL
      </th>

      <th>
        SQL
      </th>
    </tr>

    <tr>
      <td>
        ```
        SELECT Account.id, Invoice.id, ProductRatePlan.id, 
        ProductRatePlanCharge.id, RatePlan.id, 
        RatePlanCharge.id, Subscription.id 
        FROM InvoiceItem
        ```
      </td>

      <td>
        ```
        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
        ```
      </td>
    </tr>
  </table>
* Retrieve all active Subscriptions with no Rate Plan

  <table>
    <tr>
      <th>
        ZOQL
      </th>

      <th>
        SQL
      </th>
    </tr>

    <tr>
      <td>
        ```
        SELECT Subscription.id, count(RatePlanCharge.id) 
        FROM RatePlanCharge 
        WHERE Subscription.status = 'Active' 
        GROUP BY Subscription.id 
        HAVING count(RatePlanCharge.id) = 0
        ```
      </td>

      <td>
        ```
        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
        ```
      </td>
    </tr>
  </table>