This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Appendix: VMart example database schema, tables, and scripts
This appendix provides detailed information about the VMart example database’s schema, tables, and scripts.
This appendix provides detailed information about the VMart example database’s schema, tables, and scripts.
The VMart example database contains three different schemas:
-
public
-
store
-
online_sales
The term “schema” has several related meanings in Vertica:
-
In SQL statements, a schema refers to named namespace for a logical schema.
-
Logical schema refers to a set of tables and constraints.
-
Physical schema refers to a set of projections.
Tables identifies the three schemas and all the data tables in the VMart database. Each schema contains tables that are created and loaded during database installation. See the schema maps for a list of tables and their contents:
Sample scripts describes the sample scripts that contain SQL commands that represent queries that might be used in a real business using a VMart-like database. Once you’re comfortable running the example queries, you might want to write your own.
1 - Tables
The three schemas in the VMart database include the following tables:.
The three schemas in the VMart database include the following tables:
public Schema |
store Schema |
online_sales Schema |
inventory_fact |
store_orders_fact |
online_sales_fact |
customer_dimension |
store_sales_fact |
call_center_dimension |
date_dimension |
store_dimension |
online_page_dimension |
employee_dimension |
|
|
product_dimension |
|
|
promotion_dimension |
|
|
shipping_dimension |
|
|
vendor_dimension |
|
|
warehouse_dimension |
|
|
2 - Public schema map
The public schema is a snowflake schema.
The public
schema is a snowflake schema. The following graphic illustrates the public schema and its relationships with tables in the online_sales
and store
schemas.
The subsequent subsections describe database tables.
2.1 - inventory_fact
This table contains information about each product in inventory.
This table contains information about each product in inventory.
Column Name |
Data Type |
NULLs |
date_key |
INTEGER |
No |
product_key |
INTEGER |
No |
product_version |
INTEGER |
No |
warehouse_key |
INTEGER |
No |
qty_in_stock |
INTEGER |
No |
2.2 - customer_dimension
This table contains information about all the retail chain’s customers.
This table contains information about all the retail chain’s customers.
Column Name |
Data Type |
NULLs |
customer_key |
INTEGER |
No |
customer_type |
VARCHAR(16) |
Yes |
customer_name |
VARCHAR(256) |
Yes |
customer_gender |
VARCHAR(8) |
Yes |
title |
VARCHAR(8) |
Yes |
household_id |
INTEGER |
Yes |
customer_address |
VARCHAR(256) |
Yes |
customer_city |
VARCHAR(64) |
Yes |
customer_state |
CHAR(2) |
Yes |
customer_region |
VARCHAR(64) |
Yes |
marital_status |
VARCHAR(32) |
Yes |
customer_age |
INTEGER |
Yes |
number_of_children |
INTEGER |
Yes |
annual_income |
INTEGER |
Yes |
occupation |
VARCHAR(64) |
Yes |
largest_bill_amount |
INTEGER |
Yes |
store_membership_card |
INTEGER |
Yes |
customer_since |
DATE |
Yes |
deal_stage |
VARCHAR(32) |
Yes |
deal_size |
INTEGER |
Yes |
last_deal_update |
DATE |
Yes |
2.3 - date_dimension
This table contains information about dates.
This table contains information about dates. It is generated from a file containing correct date/time data.
Column Name |
Data Type |
NULLs |
date_key |
INTEGER |
No |
date |
DATE |
Yes |
full_date_description |
VARCHAR(18) |
Yes |
day_of_week |
VARCHAR(9) |
Yes |
day_number_in_calendar_month |
INTEGER |
Yes |
day_number_in_calendar_year |
INTEGER |
Yes |
day_number_in_fiscal_month |
INTEGER |
Yes |
day_number_in_fiscal_year |
INTEGER |
Yes |
last_day_in_week_indicator |
INTEGER |
Yes |
last_day_in_month_indicator |
INTEGER |
Yes |
calendar_week_number_in_year |
INTEGER |
Yes |
calendar_month_name |
VARCHAR(9) |
Yes |
calendar_month_number_in_year |
INTEGER |
Yes |
calendar_year_month |
CHAR(7) |
Yes |
calendar_quarter |
INTEGER |
Yes |
calendar_year_quarter |
CHAR(7) |
Yes |
calendar_half_year |
INTEGER |
Yes |
calendar_year |
INTEGER |
Yes |
holiday_indicator |
VARCHAR(10) |
Yes |
weekday_indicator |
CHAR(7) |
Yes |
selling_season |
VARCHAR(32) |
Yes |
2.4 - employee_dimension
This table contains information about all the people who work for the retail chain.
This table contains information about all the people who work for the retail chain.
Column Name |
Data Type |
NULLs |
employee_key |
INTEGER |
No |
employee_gender |
VARCHAR(8) |
Yes |
courtesy_title |
VARCHAR(8) |
Yes |
employee_first_name |
VARCHAR(64) |
Yes |
employee_middle_initial |
VARCHAR(8) |
Yes |
employee_last_name |
VARCHAR(64) |
Yes |
employee_age |
INTEGER |
Yes |
hire_date |
DATE |
Yes |
employee_street_address |
VARCHAR(256) |
Yes |
employee_city |
VARCHAR(64) |
Yes |
employee_state |
CHAR(2) |
Yes |
employee_region |
CHAR(32) |
Yes |
job_title |
VARCHAR(64) |
Yes |
reports_to |
INTEGER |
Yes |
salaried_flag |
INTEGER |
Yes |
annual_salary |
INTEGER |
Yes |
hourly_rate |
FLOAT |
Yes |
vacation_days |
INTEGER |
Yes |
2.5 - product_dimension
This table describes all products sold by the department store chain.
This table describes all products sold by the department store chain.
Column Name |
Data Type |
NULLs |
product_key |
INTEGER |
No |
product_version |
INTEGER |
No |
product_description |
VARCHAR(128) |
Yes |
sku_number |
CHAR(32) |
Yes |
category_description |
CHAR(32) |
Yes |
department_description |
CHAR(32) |
Yes |
package_type_description |
CHAR(32) |
Yes |
package_size |
CHAR(32) |
Yes |
fat_content |
INTEGER |
Yes |
diet_type |
CHAR(32) |
Yes |
weight |
INTEGER |
Yes |
weight_units_of_measure |
CHAR(32) |
Yes |
shelf_width |
INTEGER |
Yes |
shelf_height |
INTEGER |
Yes |
shelf_depth |
INTEGER |
Yes |
product_price |
INTEGER |
Yes |
product_cost |
INTEGER |
Yes |
lowest_competitor_price |
INTEGER |
Yes |
highest_competitor_price |
INTEGER |
Yes |
average_competitor_price |
INTEGER |
Yes |
discontinued_flag |
INTEGER |
Yes |
2.6 - promotion_dimension
This table describes every promotion ever done by the retail chain.
This table describes every promotion ever done by the retail chain.
Column Name |
Data Type |
NULLs |
promotion_key |
INTEGER |
No |
promotion_name |
VARCHAR(128) |
Yes |
price_reduction_type |
VARCHAR(32) |
Yes |
promotion_media_type |
VARCHAR(32) |
Yes |
ad_type |
VARCHAR(32) |
Yes |
display_type |
VARCHAR(32) |
Yes |
coupon_type |
VARCHAR(32) |
Yes |
ad_media_name |
VARCHAR(32) |
Yes |
display_provider |
VARCHAR(128) |
Yes |
promotion_cost |
INTEGER |
Yes |
promotion_begin_date |
DATE |
Yes |
promotion_end_date |
DATE |
Yes |
2.7 - shipping_dimension
This table contains information about shipping companies that the retail chain uses.
This table contains information about shipping companies that the retail chain uses.
Column Name |
Data Type |
NULLs |
shipping_key |
INTEGER |
No |
ship_type |
CHAR(30) |
Yes |
ship_mode |
CHAR(10) |
Yes |
ship_carrier |
CHAR(20) |
Yes |
2.8 - vendor_dimension
This table contains information about each vendor that provides products sold through the retail chain.
This table contains information about each vendor that provides products sold through the retail chain.
Column Name |
Data Type |
NULLs |
vendor_key |
INTEGER |
No |
vendor_name |
VARCHAR(64) |
Yes |
vendor_address |
VARCHAR(64) |
Yes |
vendor_city |
VARCHAR(64) |
Yes |
vendor_state |
CHAR(2) |
Yes |
vendor_region |
VARCHAR(32) |
Yes |
deal_size |
INTEGER |
Yes |
last_deal_update |
DATE |
Yes |
2.9 - warehouse_dimension
This table provides information about each of the chain’s warehouses.
This table provides information about each of the chain’s warehouses.
Column Name |
Data Type |
NULLs |
warehouse_key |
INTEGER |
No |
warehouse_name |
VARCHAR(20) |
Yes |
warehouse_address |
VARCHAR(256) |
Yes |
warehouse_city |
VARCHAR(60) |
Yes |
warehouse_state |
CHAR(2) |
Yes |
warehouse_region |
VARCHAR(32) |
Yes |
3 - Store schema map
The store schema is a snowflake schema that contains information about the retail chain’s bricks-and-mortar stores.
The store
schema is a snowflake schema that contains information about the retail chain’s bricks-and-mortar stores. The following graphic illustrates the store
schema and its relationship with tables in the public
schema.
The subsequent subsections describe database tables.
3.1 - store_orders_fact
This table contains information about all orders made at the company’s brick-and-mortar stores.
This table contains information about all orders made at the company’s brick-and-mortar stores.
Column Name |
Data Type |
NULLs |
product_key |
INTEGER |
No |
product_version |
INTEGER |
No |
store_key |
INTEGER |
No |
vendor_key |
INTEGER |
No |
employee_key |
INTEGER |
No |
order_number |
INTEGER |
No |
date_ordered |
DATE |
Yes |
date_shipped |
DATE |
Yes |
expected_delivery_date |
DATE |
Yes |
date_delivered |
DATE |
Yes |
quantity_ordered |
INTEGER |
Yes |
quantity_delivered |
INTEGER |
Yes |
shipper_name |
VARCHAR(32) |
Yes |
unit_price |
INTEGER |
Yes |
shipping_cost |
INTEGER |
Yes |
total_order_cost |
INTEGER |
Yes |
quantity_in_stock |
INTEGER |
Yes |
reorder_level |
INTEGER |
Yes |
overstock_ceiling |
INTEGER |
Yes |
3.2 - store_sales_fact
This table contains information about all sales made at the company’s brick-and-mortar stores.
This table contains information about all sales made at the company’s brick-and-mortar stores.
Column Name |
Data Type |
NULLs |
date_key |
INTEGER |
No |
product_key |
INTEGER |
No |
product_version |
INTEGER |
No |
store_key |
INTEGER |
No |
promotion_key |
INTEGER |
No |
customer_key |
INTEGER |
No |
employee_key |
INTEGER |
No |
pos_transaction_number |
INTEGER |
No |
sales_quantity |
INTEGER |
Yes |
sales_dollar_amount |
INTEGER |
Yes |
cost_dollar_amount |
INTEGER |
Yes |
gross_profit_dollar_amount |
INTEGER |
Yes |
transaction_type |
VARCHAR(16) |
Yes |
transaction_time |
TIME |
Yes |
tender_type |
VARCHAR(8) |
Yes |
3.3 - store_dimension
This table contains information about each brick-and-mortar store within the retail chain.
This table contains information about each brick-and-mortar store within the retail chain.
Column Name |
Data Type |
NULLs |
store_key |
INTEGER |
No |
store_name |
VARCHAR(64) |
Yes |
store_number |
INTEGER |
Yes |
store_address |
VARCHAR(256) |
Yes |
store_city |
VARCHAR(64) |
Yes |
store_state |
CHAR(2) |
Yes |
store_region |
VARCHAR(64) |
Yes |
floor_plan_type |
VARCHAR(32) |
Yes |
photo_processing_type |
VARCHAR(32) |
Yes |
financial_service_type |
VARCHAR(32) |
Yes |
selling_square_footage |
INTEGER |
Yes |
total_square_footage |
INTEGER |
Yes |
first_open_date |
DATE |
Yes |
last_remodel_date |
DATE |
Yes |
number_of_employees |
INTEGER |
Yes |
annual_shrinkage |
INTEGER |
Yes |
foot_traffic |
INTEGER |
Yes |
monthly_rent_cost |
INTEGER |
Yes |
4 - online_sales schema map
The online_sales schema is a snowflake schema that contains information about the retail chains.
The online_sales
schema is a snowflake schema that contains information about the retail chains. The following graphic illustrates the online_sales
schema and its relationship with tables in the public
schema.
The subsequent subsections describe database tables.
4.1 - online_sales_fact
This table describes all the items purchased through the online store front.
This table describes all the items purchased through the online store front.
Column Name |
Data Type |
NULLs |
sale_date_key |
INTEGER |
No |
ship_date_key |
INTEGER |
No |
product_key |
INTEGER |
No |
product_version |
INTEGER |
No |
customer_key |
INTEGER |
No |
call_center_key |
INTEGER |
No |
online_page_key |
INTEGER |
No |
shipping_key |
INTEGER |
No |
warehouse_key |
INTEGER |
No |
promotion_key |
INTEGER |
No |
pos_transaction_number |
INTEGER |
No |
sales_quantity |
INTEGER |
Yes |
sales_dollar_amount |
FLOAT |
Yes |
ship_dollar_amount |
FLOAT |
Yes |
net_dollar_amount |
FLOAT |
Yes |
cost_dollar_amount |
FLOAT |
Yes |
gross_profit_dollar_amount |
FLOAT |
Yes |
transaction_type |
VARCHAR(16) |
Yes |
4.2 - call_center_dimension
This table describes all the chain’s call centers.
This table describes all the chain’s call centers.
Column Name |
Data Type |
NULLs |
call_center_key |
INTEGER |
No |
cc_closed_date |
DATE |
Yes |
cc_open_date |
DATE |
Yes |
cc_date |
VARCHAR(50) |
Yes |
cc_class |
VARCHAR(50) |
Yes |
cc_employees |
INTEGER |
Yes |
cc_hours |
CHAR(20) |
Yes |
cc_manager |
VARCHAR(40) |
Yes |
cc_address |
VARCHAR(256) |
Yes |
cc_city |
VARCHAR(64) |
Yes |
cc_state |
CHAR(2) |
Yes |
cc_region |
VARCHAR(64) |
Yes |
4.3 - online_page_dimension
This table describes all the pages in the online store front.
This table describes all the pages in the online store front.
Column Name |
Data Type |
NULLs |
online_page_key |
INTEGER |
No |
start_date |
DATE |
Yes |
end_date |
DATE |
Yes |
page_number |
INTEGER |
Yes |
page_description |
VARCHAR(100) |
Yes |
page_type |
VARCHAR(100) |
Yes |
5 - Sample scripts
You can create your own queries, but the VMart example directory includes sample query script files to help you get started quickly.
You can create your own queries, but the VMart example directory includes sample query script files to help you get started quickly.
You can find the following sample scripts at this path /opt/vertica/examples/VMart_Schema
.
To run any of the scripts, enter
=> \i <script_name>
Alternatively, type the commands from the script file manually.
Note
The data that your queries return might differ from the example output shown in this guide because the sample data generator is random.
5.1 - vmart_query_01.sql
Output.
-- vmart_query_01.sql
-- FROM clause subquery
-- Return the values for five products with the
-- lowest-fat content in the Dairy department
SELECT fat_content
FROM (
SELECT DISTINCT fat_content
FROM product_dimension
WHERE department_description
IN ('Dairy') ) AS food
ORDER BY fat_content
LIMIT 5;
Output
fat_content
-------------
80
81
82
83
84
(5 rows)
5.2 - vmart_query_02.sql
Output.
-- vmart_query_02.sql
-- WHERE clause subquery
-- Asks for all orders placed by stores located in Massachusetts
-- and by vendors located elsewhere before March 1, 2003:
SELECT order_number, date_ordered
FROM store.store_orders_fact orders
WHERE orders.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
AND orders.vendor_key NOT IN (
SELECT vendor_key
FROM public.vendor_dimension
WHERE vendor_state = 'MA')
AND date_ordered < '2012-03-01';
Output
order_number | date_ordered
-------------+--------------
53019 | 2012-02-10
222168 | 2012-02-05
160801 | 2012-01-08
106922 | 2012-02-07
246465 | 2012-02-10
234218 | 2012-02-03
263119 | 2012-01-04
73015 | 2012-01-01
233618 | 2012-02-10
85784 | 2012-02-07
146607 | 2012-02-07
296193 | 2012-02-05
55052 | 2012-01-05
144574 | 2012-01-05
117412 | 2012-02-08
276288 | 2012-02-08
185103 | 2012-01-03
282274 | 2012-01-01
245300 | 2012-02-06
143526 | 2012-01-04
59564 | 2012-02-06
...
5.3 - vmart_query_03.sql
Output.
-- vmart_query_03.sql
-- noncorrelated subquery
-- Requests female and male customers with the maximum
-- annual income from customers
SELECT customer_name, annual_income
FROM public.customer_dimension
WHERE (customer_gender, annual_income) IN (
SELECT customer_gender, MAX(annual_income)
FROM public.customer_dimension
GROUP BY customer_gender);
Output
customer_name | annual_income
------------------+---------------
James M. McNulty | 999979
Emily G. Vogel | 999998
(2 rows)
5.4 - vmart_query_04.sql
Output.
-- vmart_query_04.sql
-- IN predicate
-- Find all products supplied by stores in MA
SELECT DISTINCT s.product_key, p.product_description
FROM store.store_sales_fact s, public.product_dimension p
WHERE s.product_key = p.product_key
AND s.product_version = p.product_version AND s.store_key IN (
SELECT store_key
FROM store.store_dimension
WHERE store_state = 'MA')
ORDER BY s.product_key;
Output
product_key | product_description
-------------+----------------------------------------
1 | Brand #1 butter
1 | Brand #2 bagels
2 | Brand #3 lamb
2 | Brand #4 brandy
2 | Brand #5 golf clubs
2 | Brand #6 chicken noodle soup
3 | Brand #10 ground beef
3 | Brand #11 vanilla ice cream
3 | Brand #7 canned chicken broth
3 | Brand #8 halibut
3 | Brand #9 camera case
4 | Brand #12 rash ointment
4 | Brand #13 low fat milk
4 | Brand #14 chocolate chip cookies
4 | Brand #15 silver polishing cream
5 | Brand #16 cod
5 | Brand #17 band aids
6 | Brand #18 bananas
6 | Brand #19 starch
6 | Brand #20 vegetable soup
6 | Brand #21 bourbon
...
5.5 - vmart_query_05.sql
Output.
-- vmart_query_05.sql
-- EXISTS predicate
-- Get a list of all the orders placed by all stores on
-- January 2, 2003 for the vendors with records in the
-- vendor_dimension table
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact
WHERE EXISTS (
SELECT 1
FROM public.vendor_dimension
WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
AND date_ordered = '2012-01-02';
Output
store_key | order_number | date_ordered
-----------+--------------+--------------
98 | 151837 | 2012-01-02
123 | 238372 | 2012-01-02
242 | 263973 | 2012-01-02
150 | 226047 | 2012-01-02
247 | 232273 | 2012-01-02
203 | 171649 | 2012-01-02
129 | 98723 | 2012-01-02
80 | 265660 | 2012-01-02
231 | 271085 | 2012-01-02
149 | 12169 | 2012-01-02
141 | 201153 | 2012-01-02
1 | 23715 | 2012-01-02
156 | 98182 | 2012-01-02
44 | 229465 | 2012-01-02
178 | 141869 | 2012-01-02
134 | 44410 | 2012-01-02
141 | 129839 | 2012-01-02
205 | 54138 | 2012-01-02
113 | 63358 | 2012-01-02
99 | 50142 | 2012-01-02
44 | 131255 | 2012-01-02
...
5.6 - vmart_query_06.sql
Output.
-- vmart_query_06.sql
-- EXISTS predicate
-- Orders placed by the vendor who got the best deal
-- on January 4, 2004
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key
AND vd.deal_size IN (
SELECT MAX(deal_size)
FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';
Output
store_key | order_number | date_ordered
-----------+--------------+--------------
45 | 202416 | 2013-01-04
24 | 250295 | 2013-01-04
121 | 251417 | 2013-01-04
198 | 75716 | 2013-01-04
166 | 36008 | 2013-01-04
27 | 150241 | 2013-01-04
148 | 182207 | 2013-01-04
9 | 188567 | 2013-01-04
113 | 66017 | 2013-01-04
...
5.7 - vmart_query_07.sql
Output.
-- vmart_query_07.sql
-- Multicolumn subquery
-- Which products have the highest cost,
-- grouped by category and department
SELECT product_description, sku_number, department_description
FROM public.product_dimension
WHERE (category_description, department_description, product_cost) IN (
SELECT category_description, department_description,
MAX(product_cost) FROM product_dimension
GROUP BY category_description, department_description);
Output
product_description | sku_number | department_description
---------------------------+-----------------------+---------------------------------
Brand #601 steak | SKU-#601 | Meat
Brand #649 brooms | SKU-#649 | Cleaning supplies
Brand #677 veal | SKU-#677 | Meat
Brand #1371 memory card | SKU-#1371 | Photography
Brand #1761 catfish | SKU-#1761 | Seafood
Brand #1810 frozen pizza | SKU-#1810 | Frozen Goods
Brand #1979 canned peaches | SKU-#1979 | Canned Goods
Brand #2097 apples | SKU-#2097 | Produce
Brand #2287 lens cap | SKU-#2287 | Photography
...
5.8 - vmart_query_08.sql
Output.
-- vmart_query_08.sql
-- between online_sales_fact and online_page_dimension
SELECT page_description, page_type, start_date, end_date
FROM online_sales.online_sales_fact f, online_sales.online_page_dimension d
WHERE f.online_page_key = d.online_page_key
AND page_number IN
(SELECT MAX(page_number)
FROM online_sales.online_page_dimension)
AND page_type = 'monthly' AND start_date = '2012-06-02';
Output
page_description | page_type | start_date | end_date
---------------------------+-----------+------------+-----------
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly | 2012-06-02 | 2012-06-11
(12 rows)
5.9 - vmart_query_09.sql
Output.
-- vmart_query_09.sql
-- Equi join
-- Joins online_sales_fact table and the call_center_dimension
-- table with the ON clause
SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension
ON (online_sales.online_sales_fact.call_center_key
= online_sales.call_center_dimension.call_center_key
AND sale_date_key = 156)
ORDER BY sales_dollar_amount DESC;
Output
sales_quantity | sales_dollar_amount | transaction_type | cc_name
----------------+---------------------+------------------+-------------------
7 | 589 | purchase | Central Midwest
8 | 589 | purchase | South Midwest
8 | 589 | purchase | California
1 | 587 | purchase | New England
1 | 586 | purchase | Other
1 | 584 | purchase | New England
4 | 584 | purchase | New England
7 | 581 | purchase | Mid Atlantic
5 | 579 | purchase | North Midwest
8 | 577 | purchase | North Midwest
4 | 577 | purchase | Central Midwest
2 | 575 | purchase | Hawaii/Alaska
4 | 573 | purchase | NY Metro
4 | 572 | purchase | Central Midwest
1 | 570 | purchase | Mid Atlantic
9 | 569 | purchase | Southeastern
1 | 569 | purchase | NY Metro
5 | 567 | purchase | Other
7 | 567 | purchase | Hawaii/Alaska
9 | 567 | purchase | South Midwest
1 | 566 | purchase | New England
...