SQL query example that pulls Revenue data and the necessary fields from a BigQuery table containing NetSuite data

The following is an SQL Query that pulls data needed to generate Revenue data. The data is fetched from a BigQuery table that is updated from NetSuite via the FiveTran Connector.

SELECT
    t.id AS transaction_id,
    t.tranid AS transaction_number,
    MAX(t.trandate) AS transaction_date,
    MIN(c.id) AS customer_id,
    MAX(c.externalid) AS customer_sfid,
    MAX(c.entityid) AS customer_name,
    MAX(s.id) AS subsidiary_id,
    MAX(s.name) AS subsidiary_name,
    tl.uniquekey AS line_unique_num,
    tl.item AS item_id,
    MAX(i.fullname) AS item_name,
    MAX(i.externalid) AS item_sfid,
    MAX(tl.itemtype) AS item_type,
    MAX(ABS(tl.netamount)) AS total_item_amount,
    MAX(tal.account) AS account_id,
    MAX(ap.periodname) as period_name
FROM
    `lilt-finance-operations.fivetran_netsuite.transaction` t
JOIN
    `lilt-finance-operations.fivetran_netsuite.customer` c ON t.entity = c.id
JOIN
    `lilt-finance-operations.fivetran_netsuite.transactionline` tl ON t.id = tl.transaction
JOIN
    `lilt-finance-operations.fivetran_netsuite.transactionaccountingline` tal ON t.id = tal.transaction
JOIN
    `lilt-finance-operations.fivetran_netsuite.item` i ON tl.item = i.id
JOIN
    `lilt-finance-operations.fivetran_netsuite.subsidiary` s ON tl.subsidiary = s.id
JOIN
    `lilt-finance-operations.fivetran_netsuite.accountingperiod` ap ON t.postingperiod = ap.id
WHERE
    --t.trandate >= '2025-03-01 00:00:00'
    --AND
    tal.accounttype = 'Income'
    AND tl.taxline = 'F'
    AND ap.periodname = 'Apr 2025'
GROUP BY
    t.id, t.tranid, tl.item, tl.uniquekey
ORDER BY
    t.id;