human
Schema:
Customers (customer_id, customer_first_name, customer_middle_initial, customer_last_name, gender, email_address, login_name, login_password, phone_number, town_city, state_county_province, country)
Orders (order_id, customer_id, date_order_placed, order_details)
Invoices (invoice_number, order_id, invoice_date)
Accounts (account_id, customer_id, date_account_opened, account_name, other_account_details)
Product_Categories (production_type_code, product_type_description, vat_rating)
Products (product_id, parent_product_id, production_type_code, unit_price, product_name, product_color, product_size)
Financial_Transactions (transaction_id, account_id, invoice_number, transaction_type, transaction_date, transaction_amount, transaction_comment, other_transaction_details)
Order_Items (order_item_id, order_id, product_id, product_quantity, other_order_item_details)
Invoice_Line_Items (order_item_id, invoice_number, product_id, product_title, product_quantity, product_price, derived_product_cost, derived_vat_payable, derived_total_cost)
Please answer with SQL.
assistant
Sure, I understand.
human
How many transaction does each account have? Show the number and account id.
assistant
SELECT count(*) , account_id FROM Financial_transactions
human
What is the invoice number and invoice date for the invoice with most number of transactions?
assistant
SELECT T2.invoice_number , T2.invoice_date FROM Financial_transactions AS T1 JOIN Invoices AS T2 ON T1.invoice_number = T2.invoice_number GROUP BY T1.invoice_number ORDER BY count(*) DESC LIMIT 1
human
How many transactions do we have?
assistant
SELECT count(*) FROM Financial_transactions
human
Show ids for all transactions whose amounts are greater than the average.
assistant
SELECT transaction_id FROM Financial_transactions WHERE transaction_amount > (SELECT avg(transaction_amount) FROM Financial_transactions)
human
Show the transaction types and the total amount of transactions.
assistant
SELECT transaction_type , sum(transaction_amount) FROM Financial_transactions GROUP BY transaction_type
human
What is the average, minimum, maximum, and total transaction amount?
assistant
SELECT avg ( transaction_amount ) , MIN( transaction_amount ) , MAX( transaction_amount ) , SUM( transaction_amount ) FROM Financial_transactions ;