Total transactions by item family

SQL query for Chargebee

Get the total number of transactions for each item family.

SELECT item_families.name AS item_family, COUNT(*) AS total_transactions FROM transactions JOIN item_families ON transactions.item_family_id = item_families.id GROUP BY item_family ORDER BY total_transactions DESC

Chargebee data model

Table Invoices

Invoice_id: String
Invoice_tax: Integer
Invoice_date: Integer
Invoice_total: Integer
Invoice_object: String
Invoice_status: String
Invoice_channel: String
Invoice_deleted: Boolean
Invoice_paid_at: Integer
Invoice_due_date: Integer
Invoice_is_gifted: Boolean
Invoice_recurring: Boolean
Invoice_sub_total: Integer
Invoice_amount_due: Integer
Invoice_price_type: String
Invoice_updated_at: Integer
Invoice_amount_paid: Integer
Invoice_customer_id: String
Invoice_generated_at: Integer
Invoice_currency_code: String
Invoice_exchange_rate: Number
Invoice_first_invoice: Boolean
Invoice_net_term_days: Integer
Invoice_term_finalized: Boolean
Invoice_amount_adjusted: Integer
Invoice_credits_applied: Integer
Invoice_line_items_0_id: String
Invoice_line_items_1_id: String
Invoice_subscription_id: String
Invoice_new_sales_amount: Integer
Invoice_resource_version: Integer
Invoice_round_off_amount: Integer
Invoice_write_off_amount: Integer
Invoice_amount_to_collect: Integer
Invoice_base_currency_code: String
Invoice_has_advance_charges: Boolean
Invoice_line_items_0_amount: Integer
Invoice_line_items_0_object: String
Invoice_line_items_1_amount: Integer
Invoice_line_items_1_object: String
Invoice_line_items_0_date_to: Integer
Invoice_line_items_1_date_to: Integer
Invoice_line_items_0_is_taxed: Boolean
Invoice_line_items_0_quantity: Integer
Invoice_line_items_1_is_taxed: Boolean
Invoice_line_items_1_quantity: Integer
Invoice_billing_address_object: String
Invoice_line_items_0_date_from: Integer
Invoice_line_items_0_entity_id: String
Invoice_line_items_1_date_from: Integer
Invoice_line_items_1_entity_id: String
Invoice_billing_address_company: String
Invoice_line_items_0_tax_amount: Integer
Invoice_line_items_1_tax_amount: Integer
Invoice_line_items_0_customer_id: String
Invoice_line_items_0_description: String
Invoice_line_items_0_entity_type: String
Invoice_line_items_0_unit_amount: Integer
Invoice_line_items_1_customer_id: String
Invoice_line_items_1_description: String
Invoice_line_items_1_entity_type: String
Invoice_line_items_1_unit_amount: Integer
Invoice_linked_payments_0_txn_id: String
Invoice_billing_address_last_name: String
Invoice_billing_address_first_name: String
Invoice_line_items_0_pricing_model: String
Invoice_line_items_1_pricing_model: String
Invoice_linked_payments_0_txn_date: Integer
Invoice_issued_credit_notes_0_cn_id: String
Invoice_line_items_0_discount_amount: Integer
Invoice_line_items_0_subscription_id: String
Invoice_line_items_1_discount_amount: Integer
Invoice_line_items_1_subscription_id: String
Invoice_linked_payments_0_applied_at: Integer
Invoice_linked_payments_0_txn_amount: Integer
Invoice_linked_payments_0_txn_status: String
Invoice_issued_credit_notes_0_cn_date: Integer
Invoice_issued_credit_notes_0_cn_total: Integer
Invoice_line_items_0_tax_exempt_reason: String
Invoice_line_items_1_tax_exempt_reason: String
Invoice_adjustment_credit_notes_0_cn_id: String
Invoice_issued_credit_notes_0_cn_status: String
Invoice_line_items_0_entity_description: String
Invoice_linked_payments_0_applied_amount: Integer
Invoice_adjustment_credit_notes_0_cn_date: Integer
Invoice_billing_address_validation_status: String
Invoice_adjustment_credit_notes_0_cn_total: Integer
Invoice_adjustment_credit_notes_0_cn_status: String
Invoice_issued_credit_notes_0_cn_reason_code: String
Invoice_line_items_0_item_level_discount_amount: Integer
Invoice_line_items_1_item_level_discount_amount: Integer
Invoice_adjustment_credit_notes_0_cn_reason_code: String
Invoice_issued_credit_notes_0_cn_create_reason_code: String
Invoice_adjustment_credit_notes_0_cn_create_reason_code: String

Table Customers

Card_iin: String
Card_last4: String
Card_object: String
Card_status: String
Customer_id: String
Card_gateway: String
Card_card_type: String
Card_last_name: String
Customer_email: String
Customer_phone: String
Card_created_at: Integer
Card_first_name: String
Card_updated_at: Integer
Customer_object: String
Card_customer_id: String
Card_expiry_year: Integer
Customer_channel: String
Customer_company: String
Customer_deleted: Boolean
Card_expiry_month: Integer
Card_funding_type: String
Card_masked_number: String
Customer_last_name: String
Customer_created_at: Integer
Customer_first_name: String
Customer_taxability: String
Customer_updated_at: Integer
Customer_card_status: String
Customer_pii_cleared: String
Card_resource_version: Integer
Card_payment_source_id: String
Customer_net_term_days: Integer
Card_gateway_account_id: String
Customer_auto_collection: String
Customer_excess_payments: Integer
Customer_resource_version: Integer
Customer_unbilled_charges: Integer
Customer_balances_0_object: String
Customer_allow_direct_debit: Boolean
Customer_refundable_credits: Integer
Customer_payment_method_type: String
Customer_promotional_credits: Integer
Customer_payment_method_object: String
Customer_payment_method_status: String
Customer_payment_method_gateway: String
Customer_preferred_currency_code: String
Customer_balances_0_currency_code: String
Customer_primary_payment_source_id: String
Customer_balances_0_excess_payments: Integer
Customer_balances_0_unbilled_charges: Integer
Customer_payment_method_reference_id: String
Customer_balances_0_refundable_credits: Integer
Customer_balances_0_promotional_credits: Integer
Customer_balances_0_balance_currency_code: String
Customer_payment_method_gateway_account_id: String

Table Transactions

Transaction_id: String
Transaction_date: Integer
Transaction_type: String
Transaction_amount: Integer
Transaction_object: String
Transaction_status: String
Transaction_deleted: Boolean
Transaction_gateway: String
Transaction_updated_at: Integer
Transaction_customer_id: String
Transaction_amount_unused: Integer
Transaction_currency_code: String
Transaction_exchange_rate: Number
Transaction_payment_method: String
Transaction_subscription_id: String
Transaction_resource_version: Integer
Transaction_base_currency_code: String
Transaction_linked_invoices_0_applied_at: Integer
Transaction_linked_invoices_0_invoice_id: String
Transaction_linked_invoices_0_invoice_date: Integer
Transaction_linked_invoices_0_invoice_total: Integer
Transaction_linked_invoices_0_applied_amount: Integer
Transaction_linked_invoices_0_invoice_status: String

Table Items

Table Credit Notes

Table Item Families

Magical SQL for Chargebee with Peliqan.io

Explore data in a rich spreadsheet UI. Use Magical SQL to combine and transform data. SQL queries become new tables that can be shared with business users and used in any BI tool such as Microsoft Power BI, Tableau or Metabase.

Let AI do the work for you

Peliqan’s AI assistant helps you to write SQL queries to get to insights fast. Ask your question in plain English and immediately see the result in Peliqan’s rich spreadsheet viewer.