# Oci Order Detail Query

> Query and analyze OCI order detail data from StarRocks via DuckDB. Always exports to CSV before analysis. Supports basic, aggregation, customer, sales, time-series, contract, subscription, credits, discount, and cooperation mode analysis.

- Skill: `agw-sales-ops/oci-order-detail-query` (Agent Skill)
- Install (CLI): `npx skillmds@latest add agw-sales-ops/oci-order-detail-query`
- Raw SKILL.md: https://api.skillmd.com/api/skills/agw-sales-ops/oci-order-detail-query/raw
- Safety review: WARNING
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: agw-sales-ops (https://skillmd.com/u/agw-sales-ops)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/agw-sales-ops/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)

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

After installation, DuckDB binary is at `~/.duckdb/cli/latest/duckdb`. Add to PATH if needed:
```bash
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:
```bash
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

```bash
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:**

```bash
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:
```bash
# 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:

```bash
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:

```bash
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):

```bash
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:

```bash
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:

```bash
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:

```bash
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):

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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):

```bash
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
| Field | Type | Description |
|-------|------|-------------|
| order_number | VARCHAR | Order Number - unique order identifier |
| opp_id | VARCHAR | Opp ID - opportunity tracking identifier |
| source_id | VARCHAR | SourceID - source system identifier |

### Customer Fields
| Field | Type | Description |
|-------|------|-------------|
| order_customer_name | VARCHAR | 下单客户名称 - customer name (supports LIKE fuzzy search) |
| cooperation_mode | VARCHAR | 合作模式 - cooperation mode / partnership type |
| order_entity | VARCHAR | 下单主体 - ordering entity / legal entity |

### Sales Fields
| Field | Type | Description |
|-------|------|-------------|
| sales_name | VARCHAR | 敏捷云销售 (人员) - AgileCDN sales representative |
| oracle_sales | VARCHAR | Oracle销售 - Oracle sales representative |

### Date Fields
| Field | Type | Description |
|-------|------|-------------|
| order_date | DATE | 下单日期 - order placement date (**primary date filter**) |
| contract_start_date | DATE | 订单合同起始日期 - contract start date |
| contract_end_date | DATE | 订单合同结束日期 - contract end date |

### Amount Fields
| Field | Type | Description |
|-------|------|-------------|
| credits_amount | DECIMAL | Credits数量 - credits quantity |
| order_amount_with_tax | DECIMAL | 下单金额（含税） - order amount including tax (**primary amount metric**) |
| od_product_discount | DECIMAL | OD协议产品折扣 - OD product discount rate |

### Subscription Fields
| Field | Type | Description |
|-------|------|-------------|
| subscription_plan_number | VARCHAR | Subscription Plan Number - for tracking Oracle invoices |
| subscription_id | VARCHAR | Subscription ID - for cost accounting |
| subscription_no_id | VARCHAR | 订阅号ID - subscription number ID |

## 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.