Coreties Technical Assessment - Garo Nazarian

Quick Links

🔗 Live Demo: https://coreties-technical-test-production.up.railway.app/companies
📦 GitHub: https://github.com/Garabed96/coreties-technical-test
📸 Screenshots: https://github.com/Garabed96/coreties-technical-test/tree/main/screenshots

---

Key Implementation Highlights

SQL Aggregations

Used CTEs with UNION ALL to deduplicate companies across importer/exporter roles, with composite key (name, country) for accurate grouping.

Server-Side Pagination & Search

20 companies per page with debounced search that filters across all records (not just current page).

Type Safety

Zod schemas for runtime validation at API boundaries with type coercion for DuckDB numeric output.

API Design

Separated /api/companies/stats endpoint to prevent unnecessary refetches during pagination.

Testing

Vitest test suite covering happy paths, error cases, SQL logic, and security (SQL injection prevention).

Deployment

Deployed on Railway with DuckDB native module support and sub-millisecond query performance on 5K records.

---

All checklist items complete. Development notes, design decisions, and architecture details documented in DEVELOPER.md.

Full-Stack Technical Assessment

Quick Start

npm install
npm run dev

Open http://localhost:3000

---

Your Tasks

Transform shipment data into company analytics. The /companies page has scaffolded UI with fake data - wire it up with real SQL queries.

Checklist

• Define Company interface in types/company.ts
• Implement transformShipmentsToCompanies() in lib/data/shipments.ts using SQL
• Create API endpoint(s) in pages/api/
• Wire up "Total Companies" card (count importers/exporters)
• Wire up "Top 5 Commodities" card (aggregate by weight)
• Wire up "Monthly Volume" chart (kg per month)
• Display company list table with real aggregated data
• Implement company detail panel (loads when clicking a company)

---

What We're Evaluating

• SQL - aggregations, GROUP BY, filtering, date functions
• Full-stack integration - API design, data flow, frontend state
• Domain modeling - how you structure the Company type
• Code clarity - readable, maintainable code

---

Supplemental Material

About the Data

~5,000 shipment records in data/shipments.json:

interface Shipment {
  id: string;
  importer_name: string;
  importer_country: string;
  importer_website: string;
  exporter_name: string;
  exporter_country: string;
  exporter_website: string;
  shipment_date: string; // ISO-8601
  commodity_name: string;
  industry_sector: string;
  weight_metric_tonnes: number;
}

SQL Reference

Data is pre-loaded into a shipments table. Use the query() helper:

import { query } from '@/lib/data/shipments';

const results = await query<{ name: string; total: number }>(`
  SELECT importer_name as name, COUNT(*) as total
  FROM shipments
  GROUP BY importer_name
`);

DuckDB date functions: duckdb.org/docs/sql/functions/date

Context

Coreties analyzes shipment customs data. A "shipment" = goods moving from an exporter (seller) to an importer (buyer) across countries.

---

Screenshots

Homepage

Companies Page