Exporting Results For Reporting

• Headers present and human-readable
• Dates/times in correct timezone and format
• Numbers rounded/typed correctly (no thousand separators)
• Delimiter consistent (CSV or TSV) and quotes for text
• NULLs represented as empty cells (unless otherwise agreed)
• UTF-8 encoding to avoid garbled characters
• No sensitive data unless authorized

Goal: Export monthly revenue by region as CSV with a header.

-- Query (PostgreSQL)
\copy (
  SELECT date_trunc('month', o.order_date)::date AS month,
         c.region,
         ROUND(SUM(o.total_usd)::numeric, 2) AS revenue_usd
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.status = 'completed'
  GROUP BY 1, 2
  ORDER BY 1, 2
) TO 'monthly_revenue_by_region.csv' CSV HEADER

Notes: \copy runs from your client and writes to your local file. Ensure you have permission to write to the target folder.

Goal: Export order details where product names may contain commas. Use TSV.

SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month,
       region,
       ROUND(SUM(total_usd), 2) AS revenue_usd
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE status = 'completed'
GROUP BY 1,2
ORDER BY 1,2
INTO OUTFILE '/tmp/monthly_revenue_by_region.tsv'
FIELDS TERMINATED BY '\t' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Notes: The OUTFILE path must be writable by the server and allowed by configuration. Adjust path as needed.

Goal: Export top customers to CSV.

-- In T-SQL
WITH revenue AS (
  SELECT c.CustomerID,
         c.Region,
         SUM(o.TotalUSD) AS RevenueUSD
  FROM dbo.Orders o
  JOIN dbo.Customers c ON c.CustomerID = o.CustomerID
  WHERE o.Status = 'completed'
  GROUP BY c.CustomerID, c.Region
)
SELECT TOP 100 WITH TIES CustomerID, Region, RevenueUSD
FROM revenue
ORDER BY RevenueUSD DESC;

Then use command-line bcp (run from your machine):

bcp "SELECT TOP 100 WITH TIES CustomerID, Region, RevenueUSD FROM MyDB.dbo.Revenue ORDER BY RevenueUSD DESC" queryout top_customers.csv -c -t, -S SERVERNAME -d MyDB -T

Options: -c (char), -t, sets comma delimiter, -T uses trusted connection. Adjust credentials as needed.

.headers on
.mode csv
.output orders_sample.csv
SELECT order_id, customer_id, date(order_date) AS order_date, total_usd
FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date
LIMIT 1000;
.output stdout

\copy (
  SELECT COALESCE(json_agg(t), '[]')
  FROM (
    SELECT o.id AS order_id,
           to_char(o.order_date, 'YYYY-MM-DD') AS order_date,
           c.region,
           ROUND(o.total_usd::numeric, 2) AS total_usd
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'completed'
    ORDER BY o.order_date
  ) t
) TO 'orders.json'

1. Select only needed columns
2. Alias columns to stakeholder-friendly names
3. COALESCE and ROUND numerics
4. Normalize dates/times and timezone
5. Sort rows and limit if sampling
6. Export with headers, UTF-8

Tables: orders(id, customer_id, order_date, status, total_usd), customers(id, region)

• Task: Export a CSV with columns: month (YYYY-MM-01), region, revenue_usd.
• Rules: Only completed orders; include header; UTF-8; two decimal places; sorted by month, region.

Expected output (first lines):

month,region,revenue_usd
2024-01-01,EMEA,102345.27
2024-01-01,NAMER,99321.40
...

-- PostgreSQL
\copy (
  SELECT date_trunc('month', o.order_date)::date AS month,
         c.region,
         ROUND(SUM(o.total_usd)::numeric, 2) AS revenue_usd
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.status = 'completed'
  GROUP BY 1, 2
  ORDER BY 1, 2
) TO 'monthly_revenue_by_region.csv' CSV HEADER;

-- MySQL/MariaDB (CSV)
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS month,
       c.region,
       ROUND(SUM(total_usd), 2) AS revenue_usd
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE status = 'completed'
GROUP BY 1,2
ORDER BY 1,2
INTO OUTFILE '/tmp/monthly_revenue_by_region.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- SQL Server (bcp with queryout)
WITH revenue AS (
  SELECT CONVERT(date, DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)) AS month,
         c.Region,
         ROUND(SUM(o.TotalUSD), 2) AS revenue_usd
  FROM dbo.Orders o
  JOIN dbo.Customers c ON c.Id = o.CustomerId
  WHERE o.Status = 'completed'
  GROUP BY CONVERT(date, DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)), c.Region
)
SELECT month, Region, revenue_usd FROM revenue ORDER BY month, Region;
-- Then export with bcp:
-- bcp "SELECT month, Region, revenue_usd FROM MyDB.dbo.revenue ORDER BY month, Region" queryout monthly_revenue_by_region.csv -c -t, -S SERVER -d MyDB -T

Tables: customers(id, company_name, region), orders(id, customer_id, status, total_usd)

• Task: Export a JSON array of top 100 customers by total_usd (completed only). Keys: customer_id, company_name, region, revenue_usd.
• Rules: revenue as number (2 decimals), sorted descending.

Expected output (snippet):

[
  {"customer_id": 42, "company_name": "Acme Corp", "region": "EMEA", "revenue_usd": 120345.25},
  {"customer_id": 7, "company_name": "BlueSky", "region": "APAC", "revenue_usd": 118930.10}
]

-- PostgreSQL
\copy (
  WITH revenue AS (
    SELECT c.id AS customer_id,
           c.company_name,
           c.region,
           ROUND(SUM(o.total_usd)::numeric, 2) AS revenue_usd
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY c.id, c.company_name, c.region
  )
  SELECT COALESCE(json_agg(t), '[]')
  FROM (
    SELECT * FROM revenue ORDER BY revenue_usd DESC, customer_id LIMIT 100
  ) t
) TO 'top_customers.json';

-- MySQL/MariaDB
SELECT JSON_ARRAYAGG(JSON_OBJECT(
  'customer_id', customer_id,
  'company_name', company_name,
  'region', region,
  'revenue_usd', revenue_usd
)) AS json_data
FROM (
  SELECT c.id AS customer_id, c.company_name, c.region,
         ROUND(SUM(o.total_usd), 2) AS revenue_usd
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE o.status = 'completed'
  GROUP BY c.id, c.company_name, c.region
  ORDER BY revenue_usd DESC, customer_id
  LIMIT 100
) t
INTO OUTFILE '/tmp/top_customers.json';

• Open in a plain-text editor: inspect header, delimiter, and a few rows
• Open in Excel/Sheets: verify columns align; sort and filter
• Re-run query count vs. rows exported (if applicable)
• Confirm date range and timezone match request

• Compute cpl_usd as cost_usd / NULLIF(leads, 0)
• Filter WHERE leads > 0
• Use a date format function if needed

-- Example (PostgreSQL idea)
\copy (
  SELECT campaign_id,
         campaign_name,
         to_char(start_date, 'YYYY-MM-DD') AS start_date,
         to_char(end_date, 'YYYY-MM-DD') AS end_date,
         leads,
         ROUND(cost_usd::numeric, 2) AS cost_usd,
         ROUND((cost_usd / NULLIF(leads,0))::numeric, 2) AS cpl_usd
  FROM campaigns
  WHERE leads > 0
  ORDER BY start_date
) TO 'marketing_campaigns.tsv' WITH (FORMAT csv, DELIMITER E'\t', HEADER true)