FYI--Here are the table creation statements for the tables involved in the sample query.
-- user_id_sequence
CREATE SEQUENCE user_id_sequence INCREMENT 1 START 100;
-- users
-- table for basic user data
CREATE TABLE users (
id INTEGER NOT NULL PRIMARY KEY -- unique id for each user
DEFAULT nextval('user_id_sequence'),
f_name VARCHAR(128) NOT NULL, -- user's first name
l_name VARCHAR(192) NOT NULL, -- user's last name
email VARCHAR(256) UNIQUE NOT NULL, -- user's email address
password VARCHAR(16) NOT NULL, -- user's password
hint VARCHAR(256), -- hint for password
dt_registered TIMESTAMP DEFAULT timestamp('now'), --timestamp format is: YYYY-MM-DD HH:MM:SS-05
user_generated_p CHAR(1) NOT NULL -- user-generated predicate
CHECK( user_generated_p IN('t', 'f')),
-- true or false specified
opt_in_p CHAR(1) NOT NULL -- opt in value
DEFAULT 't' -- true or false specified
CHECK( opt_in_p IN('t', 'f'))
);
-- user_demographics
-- table to hold user's demographics data
CREATE TABLE users_demographics (
users_id INTEGER NOT NULL PRIMARY KEY -- user's id
REFERENCES users(id),
sal VARCHAR(5) CHECK( sal IN('MR', 'MS', 'MISS', 'MRS', 'OTHER')),
-- salutation
addr1 VARCHAR(256), -- user's address line 1
addr2 VARCHAR(256), -- user's address line 2
city VARCHAR(64), -- user's city
state CHAR(2) NOT NULL, -- state of residence
zip CHAR(5) NOT NULL, -- zip code
area_code VARCHAR(3) NOT NULL, -- area code
phone VARCHAR(15), -- phone number
country CHAR(2) NOT NULL
REFERENCES country_codes(code)
DEFAULT 'US',
-- country of residence
income VARCHAR(7) REFERENCES income_codes(code),
-- user's income level
gender CHAR(1) NOT NULL -- gender
CHECK( gender IN('m', 'f', 'u')),
-- male or female or specified
birth_year INTEGER, -- birth year
birth_month INTEGER, -- birth month
birth_day INTEGER -- birth day
);
