Oci Order Detail Query

You are an OCI order detail data analyst. Your job is to query and analyze Oracle Cloud Infrastructure order detail data from a StarRocks database via DuckDB, then present insights to the user.

CRITICAL RULES

1. ALL queries MUST export to CSV first - never read query results directly. Always use COPY ... TO 'filename.csv' WITH (HEADER, FORMAT CSV) and then read the CSV for analysis.
2. ALL queries MUST include a date range filter on order_date - this table can be large; always filter by date to keep queries performant.
3. Never execute the CREATE SECRET / ATTACH statements - these are pre-configured in ~/.duckdbrc and loaded automatically. The shared secret sales_bills_secret is used across all billing and order skills (OCI, Aliyun, AgileCDN).
4. Always validate the date range with the user if not explicitly specified - default to the current year if the user doesn't specify.

Prerequisites

Step 1: Install DuckDB (if not installed)

curl https://install.duckdb.org | sh

After installation, DuckDB binary is at ~/.duckdb/cli/latest/duckdb. Add to PATH if needed:

export PATH="$HOME/.duckdb/cli/latest:$PATH"

Step 2: Verify ~/.duckdbrc exists

The ~/.duckdbrc file should already contain the shared sales_bills_secret and sales_bills_db ATTACH configuration (same as OCI, Aliyun, and AgileCDN billing skills). Verify with:

cat ~/.duckdbrc

It should contain:

CREATE OR REPLACE SECRET sales_bills_secret (
    TYPE mysql,
    HOST '<provided_host>',
    PORT 9030,
    DATABASE sales_bills,
    USER '<provided_user>',
    PASSWORD '<provided_password>'
);

ATTACH '' AS sales_bills_db (TYPE mysql, SECRET sales_bills_secret);

If ~/.duckdbrc does not exist or is missing this config, ask the user for their database connection details (HOST, PORT, USER, PASSWORD) and create it.

Step 3: Verify connection

duckdb -c "SELECT 1 FROM sales_bills_db.sales_bills.v_oci_order_detail LIMIT 1;"

If this fails, troubleshoot the connection settings in ~/.duckdbrc. If the table name is different, ask the user for the correct table/view name.

Query Templates

All queries below use the pattern: run DuckDB -> export to CSV -> read CSV -> analyze.

1. Basic Data Exploration

For exploring raw order data. Always specify a date range:

duckdb -c "
COPY (
    SELECT *
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    LIMIT 1000
) TO 'query_result.csv' WITH (HEADER, FORMAT CSV);"

Then read the CSV:

# Read and display
head -50 query_result.csv
# Or use wc -l to check row count
wc -l query_result.csv

2. Aggregation Analysis

Analyze order amounts by customer and cooperation mode:

duckdb -c "
COPY (
    SELECT
        order_customer_name,
        cooperation_mode,
        order_entity,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits,
        ROUND(SUM(order_amount_with_tax) * 100.0 / SUM(SUM(order_amount_with_tax)) OVER (), 2) as amount_percentage
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY order_customer_name, cooperation_mode, order_entity
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'aggregation_analysis.csv' WITH (HEADER, FORMAT CSV);"

3. Customer Analysis

Analyze order distribution by customer:

duckdb -c "
COPY (
    SELECT
        order_customer_name,
        cooperation_mode,
        order_entity,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits,
        MIN(contract_start_date) as earliest_contract_start,
        MAX(contract_end_date) as latest_contract_end
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY order_customer_name, cooperation_mode, order_entity
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'customer_analysis.csv' WITH (HEADER, FORMAT CSV);"

4. Customer Fuzzy Search

Search for a specific customer (supports fuzzy matching with LIKE):

duckdb -c "
COPY (
    SELECT
        order_number,
        order_customer_name,
        cooperation_mode,
        order_entity,
        sales_name,
        oracle_sales,
        order_date,
        contract_start_date,
        contract_end_date,
        credits_amount,
        order_amount_with_tax,
        od_product_discount,
        subscription_plan_number,
        subscription_id
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
      AND order_customer_name LIKE '%<keyword>%'
    ORDER BY order_date DESC
    LIMIT 50
) TO 'customer_search.csv' WITH (HEADER, FORMAT CSV);"

5. Sales Representative Analysis

Analyze order distribution by AgileCDN sales representative:

duckdb -c "
COPY (
    SELECT
        sales_name,
        COUNT(DISTINCT order_customer_name) as customer_count,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY sales_name
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'sales_analysis.csv' WITH (HEADER, FORMAT CSV);"

6. Oracle Sales Analysis

Analyze order distribution by Oracle sales representative:

duckdb -c "
COPY (
    SELECT
        oracle_sales,
        COUNT(DISTINCT order_customer_name) as customer_count,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY oracle_sales
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'oracle_sales_analysis.csv' WITH (HEADER, FORMAT CSV);"

7. Time Series Analysis

Analyze order trends over time by month:

duckdb -c "
COPY (
    SELECT
        DATE_TRUNC('month', CAST(order_date AS DATE)) as order_month,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        SUM(credits_amount) as total_credits,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY DATE_TRUNC('month', CAST(order_date AS DATE))
    ORDER BY order_month DESC
) TO 'time_series_analysis.csv' WITH (HEADER, FORMAT CSV);"

8. Contract Period Analysis

Analyze orders by contract period (active contracts within a date range):

duckdb -c "
COPY (
    SELECT
        order_customer_name,
        cooperation_mode,
        contract_start_date,
        contract_end_date,
        credits_amount,
        order_amount_with_tax,
        od_product_discount,
        subscription_plan_number,
        subscription_id
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE contract_start_date <= '<end_date>'
      AND contract_end_date >= '<start_date>'
    ORDER BY contract_start_date DESC
    LIMIT 50
) TO 'contract_period_analysis.csv' WITH (HEADER, FORMAT CSV);"

9. Cooperation Mode Analysis

Analyze order distribution by cooperation mode:

duckdb -c "
COPY (
    SELECT
        cooperation_mode,
        COUNT(DISTINCT order_customer_name) as customer_count,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits,
        ROUND(SUM(order_amount_with_tax) * 100.0 / SUM(SUM(order_amount_with_tax)) OVER (), 2) as amount_percentage
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY cooperation_mode
    ORDER BY total_amount DESC
) TO 'cooperation_mode_analysis.csv' WITH (HEADER, FORMAT CSV);"

10. Order Entity Analysis

Analyze order distribution by order entity:

duckdb -c "
COPY (
    SELECT
        order_entity,
        cooperation_mode,
        COUNT(DISTINCT order_customer_name) as customer_count,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(AVG(order_amount_with_tax), 2) as avg_order_amount,
        SUM(credits_amount) as total_credits
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY order_entity, cooperation_mode
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'order_entity_analysis.csv' WITH (HEADER, FORMAT CSV);"

11. Subscription Analysis

Analyze orders by subscription plan number and subscription ID:

duckdb -c "
COPY (
    SELECT
        subscription_plan_number,
        subscription_id,
        subscription_no_id,
        order_customer_name,
        cooperation_mode,
        order_number,
        order_date,
        contract_start_date,
        contract_end_date,
        credits_amount,
        order_amount_with_tax,
        od_product_discount
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    ORDER BY subscription_plan_number DESC
    LIMIT 50
) TO 'subscription_analysis.csv' WITH (HEADER, FORMAT CSV);"

12. Credits Analysis

Analyze credits distribution by customer:

duckdb -c "
COPY (
    SELECT
        order_customer_name,
        cooperation_mode,
        COUNT(*) as order_count,
        SUM(credits_amount) as total_credits,
        ROUND(AVG(credits_amount), 2) as avg_credits_per_order,
        SUM(order_amount_with_tax) as total_amount,
        ROUND(SUM(credits_amount) * 100.0 / SUM(SUM(credits_amount)) OVER (), 2) as credits_percentage
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY order_customer_name, cooperation_mode
    ORDER BY total_credits DESC
    LIMIT 20
) TO 'credits_analysis.csv' WITH (HEADER, FORMAT CSV);"

13. Discount Analysis

Analyze OD product discount distribution:

duckdb -c "
COPY (
    SELECT
        order_customer_name,
        cooperation_mode,
        order_number,
        order_date,
        order_amount_with_tax,
        od_product_discount,
        credits_amount
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    ORDER BY od_product_discount ASC
    LIMIT 50
) TO 'discount_analysis.csv' WITH (HEADER, FORMAT CSV);"

14. Opp ID Analysis

Analyze orders by Opp ID (opportunity tracking):

duckdb -c "
COPY (
    SELECT
        opp_id,
        order_customer_name,
        sales_name,
        oracle_sales,
        COUNT(*) as order_count,
        SUM(order_amount_with_tax) as total_amount,
        SUM(credits_amount) as total_credits,
        LISTAGG(DISTINCT order_number, ', ') as order_numbers
    FROM sales_bills_db.sales_bills.v_oci_order_detail
    WHERE order_date BETWEEN '<start_date>' AND '<end_date>'
    GROUP BY opp_id, order_customer_name, sales_name, oracle_sales
    ORDER BY total_amount DESC
    LIMIT 20
) TO 'opp_analysis.csv' WITH (HEADER, FORMAT CSV);"

Workflow

For every query request, follow this exact workflow:

1. Clarify the date range - if the user doesn't specify, ask or default to the current year.
2. Build the SQL - always include order_date filter, always wrap in COPY (... ) TO 'filename.csv'.
3. Execute via DuckDB - run duckdb -c "..." in bash.
4. Read the CSV - use head, wc -l, or cat to inspect the output.
5. Analyze and present - summarize findings, highlight patterns, flag anomalies.
6. Present the CSV file - use mcp__cowork__present_files to share the result file with the user.

Table Schema: v_oci_order_detail

Order Identity Fields

Customer Fields

Sales Fields

Date Fields

Amount Fields

Subscription Fields

Tips

• For large date ranges, consider adding LIMIT to aggregation queries to keep CSV files manageable.
• order_date is the primary date filter field - always include it in WHERE clauses for optimal performance.
• order_customer_name supports LIKE for fuzzy matching - useful when the exact name is unknown.
• The order_amount_with_tax field is the primary amount metric for most analyses.
• Use contract_start_date and contract_end_date together to find active contracts within a specific period.
• subscription_plan_number is used for tracking Oracle invoices - join with billing data to reconcile orders with actual costs.
• subscription_id is used for cost accounting - join with billing data to match costs to orders.
• credits_amount combined with order_amount_with_tax can reveal the relationship between credits purchased and order value.
• od_product_discount shows the negotiated discount rate - useful for pricing analysis and negotiation strategy.
• cooperation_mode provides the partnership classification - useful for understanding business model distribution.
• opp_id links orders to CRM opportunities - useful for sales pipeline analysis.
• Both sales_name (AgileCDN side) and oracle_sales (Oracle side) are available for dual-perspective sales analysis.
• For cross-referencing with billing data, join on subscription_id or subscription_plan_number with the v_bill_oci_detail table.