Spaces:
Sleeping
Sleeping
| -- Generated by backend/ddl_docs.py from live Postgres introspection. | |
| -- Do not edit by hand: `python -m backend.ddl_docs` rewrites it. | |
| CREATE TABLE orders ( | |
| order_id character varying NOT NULL, | |
| client_id character varying, | |
| order_date date, | |
| delivery_date date, | |
| carrier character varying, | |
| origin_city character varying, | |
| destination_city character varying, | |
| status character varying, | |
| sku character varying, | |
| product_category character varying, | |
| quantity integer, | |
| unit_price_usd numeric(10,2), | |
| order_value_usd numeric(12,2), | |
| is_promo boolean, | |
| promo_discount_pct integer, | |
| region character varying, | |
| warehouse character varying, | |
| PRIMARY KEY (order_id) | |
| ); | |
| -- Comments | |
| COMMENT ON TABLE orders IS 'Table `orders` is the single source of truth for logistics analytics: one row per customer order, 400 rows total, no other table exists and there are no joins. Columns: order_id, client_id, order_date, delivery_date, carrier, origin_city, destination_city, status, sku, product_category, quantity, unit_price_usd, order_value_usd, is_promo, promo_discount_pct, region, warehouse.'; | |
| COMMENT ON COLUMN orders.order_id IS 'primary key. Unique id of one order, e.g. ''ORD-100001''. Count it for order volume. 400 distinct values (too many to list).'; | |
| COMMENT ON COLUMN orders.client_id IS '. Customer id, e.g. ''CUST-1007''. 30 distinct clients. 30 distinct values: ''CL-1001'', ''CL-1002'', ''CL-1003'', ''CL-1004'', ''CL-1005'', ''CL-1006'', ''CL-1007'', ''CL-1008'', ''CL-1009'', ''CL-1010'', ''CL-1011'', ''CL-1012'', ''CL-1013'', ''CL-1014'', ''CL-1015'', ''CL-1016'', ''CL-1017'', ''CL-1018'', ''CL-1019'', ''CL-1020'', ''CL-1021'', ''CL-1022'', ''CL-1023'', ''CL-1024'', ''CL-1025'', ''CL-1026'', ''CL-1027'', ''CL-1028'', ''CL-1029'', ''CL-1030''.'; | |
| COMMENT ON COLUMN orders.order_date IS 'never null. When the order was placed. Range 2025-01-01..2025-12-30. Use date_trunc(''month'', order_date) for monthly trends and to_char(order_date, ''IYYY-"W"IW'') for ISO weeks.'; | |
| COMMENT ON COLUMN orders.delivery_date IS 'nullable. When the order was delivered. NULL when it has not been delivered (in_transit, canceled, some exception rows). Delivery duration in days = delivery_date - order_date.'; | |
| COMMENT ON COLUMN orders.carrier IS '. Shipping company. 9 distinct values: ''BluePost'', ''CargoLux'', ''DHL'', ''FedEx'', ''GLS'', ''NordExpress'', ''PostNL'', ''UPS'', ''USPS''. 9 distinct values: ''DHL'', ''DPD'', ''FedEx'', ''GLS'', ''LaserShip'', ''OnTrac'', ''Royal Mail'', ''UPS'', ''USPS''.'; | |
| COMMENT ON COLUMN orders.origin_city IS '. Ship-from city formatted ''City, ST'', e.g. ''Chicago, IL''. 9 distinct values: ''Amsterdam, NL'', ''Atlanta, GA'', ''Berlin, DE'', ''Chicago, IL'', ''Dallas, TX'', ''London, UK'', ''Los Angeles, CA'', ''Newark, NJ'', ''San Francisco, CA''.'; | |
| COMMENT ON COLUMN orders.destination_city IS '. Ship-to city formatted ''City, ST'', e.g. ''Boston, MA''. 47 distinct values: ''Austin, TX'', ''Baltimore, MD'', ''Birmingham, UK'', ''Boise, ID'', ''Boston, MA'', ''Brussels, BE'', ''Charlotte, NC'', ''Cleveland, OH'', ''Cologne, DE'', ''Denver, CO'', ''Detroit, MI'', ''Edinburgh, UK'', ''Frankfurt, DE'', ''Glasgow, UK'', ''Hamburg, DE'', ''Houston, TX'', ''Indianapolis, IN'', ''Kansas City, MO'', ''Las Vegas, NV'', ''Leeds, UK'', ''Manchester, UK'', ''Milwaukee, WI'', ''Minneapolis, MN'', ''Munich, DE'', ''Nashville, TN'', ''New York, NY'', ''Oklahoma City, OK'', ''Orlando, FL'', ''Paris, FR'', ''Philadelphia, PA'', ''Phoenix, AZ'', ''Portland, OR'', ''Prague, CZ'', ''Reno, NV'', ''Richmond, VA'', ''Sacramento, CA'', ''Salt Lake City, UT'', ''San Antonio, TX'', ''San Diego, CA'', ''San Jose, CA'', ''Seattle, WA'', ''St. Louis, MO'', ''Tampa, FL'', ''Vienna, AT'', ''Warsaw, PL'', ''Washington, DC'', ''Zurich, CH''.'; | |
| COMMENT ON COLUMN orders.status IS '. Order state, exactly one of ''delivered'', ''delayed'', ''in_transit'', ''exception'', ''canceled''. ''delayed'' means late; ''exception'' means a delivery problem. On-time rate = delivered / (delivered + delayed). 5 distinct values: ''canceled'', ''delayed'', ''delivered'', ''exception'', ''in_transit''.'; | |
| COMMENT ON COLUMN orders.sku IS '. Product code formatted ''<CATEGORY>-<4 digits>'', e.g. ''PAPER-0197''. 355 distinct SKUs. 355 distinct values (too many to list).'; | |
| COMMENT ON COLUMN orders.product_category IS '. Product family, one of ''BOOK'', ''BRUSH'', ''CRAYON'', ''MARKER'', ''PAINT'', ''PAPER'', ''PENCIL'', ''STICKER''. 8 distinct values: ''BOOK'', ''BRUSH'', ''CRAYON'', ''MARKER'', ''PAINT'', ''PAPER'', ''PENCIL'', ''STICKER''.'; | |
| COMMENT ON COLUMN orders.quantity IS '. Units ordered in this order.'; | |
| COMMENT ON COLUMN orders.unit_price_usd IS '. Price per unit in USD.'; | |
| COMMENT ON COLUMN orders.order_value_usd IS '. Total order value in USD after discount. Sum it for revenue.'; | |
| COMMENT ON COLUMN orders.is_promo IS '. True when the order used a promotion.'; | |
| COMMENT ON COLUMN orders.promo_discount_pct IS '. Discount percent applied, 0 when is_promo is false.'; | |
| COMMENT ON COLUMN orders.region IS '. Sales region, one of ''EU'', ''UK'', ''US-C'', ''US-E'', ''US-W''. 5 distinct values: ''EU'', ''UK'', ''US-C'', ''US-E'', ''US-W''.'; | |
| COMMENT ON COLUMN orders.warehouse IS '. Fulfilment centre code, 9 distinct: ''AMS-FC1'', ''ATL-DC1'', ''BER-FC1'', ''CHI-DC1'', ''DFW-DC1'', ''EWR-DC1'', ''LAX-DC1'', ''LON-FC1'', ''SFO-DC2''. 9 distinct values: ''AMS-FC1'', ''ATL-DC1'', ''BER-FC1'', ''CHI-DC1'', ''DFW-DC1'', ''EWR-DC1'', ''LAX-DC1'', ''LON-FC1'', ''SFO-DC2''.'; | |