CREATE TABLE agencies (
agency_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT NOT NULL,
operating_states TEXT NOT NULL,
start_year INTEGER NOT NULL,
sale_commission REAL NOT NULL -- Cut taken on each sale made by its agents
);
CREATE TABLE agents (
agent_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT NOT NULL,
license_number TEXT UNIQUE NOT NULL,
agency_id integer NOT NULL,
FOREIGN KEY (agency_id) REFERENCES agencies (agency_id)
);
CREATE TABLE houses (
house_id INTEGER PRIMARY KEY AUTOINCREMENT,
address TEXT NOT NULL,
city TEXT NOT NULL,
state TEXT NOT NULL,
zip_code TEXT NOT NULL,
listing_price REAL NOT NULL,
listing_date TEXT NOT NULL,
status TEXT CHECK(status IN ('Available', 'Under Contract', 'Sold')) NOT NULL
);
CREATE TABLE house_features (
feature_id INTEGER PRIMARY KEY AUTOINCREMENT,
house_id INTEGER,
feature_type TEXT NOT NULL,
description TEXT NOT NULL,
FOREIGN KEY (house_id) REFERENCES houses(house_id)
);
CREATE TABLE people (
person_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER NOT NULL,
email TEXT UNIQUE NOT NULL,
phone TEXT NOT NULL
);
CREATE TABLE viewings (
viewing_id INTEGER PRIMARY KEY AUTOINCREMENT,
house_id INTEGER,
person_id INTEGER,
viewing_date TEXT NOT NULL,
FOREIGN KEY (house_id) REFERENCES houses(house_id)
FOREIGN KEY (person_id) REFERENCES people(person_id)
);
CREATE TABLE sales (
sale_id INTEGER PRIMARY KEY AUTOINCREMENT,
house_id INTEGER NOT NULL,
seller_agent_id INTEGER NOT NULL,
buyer_id INTEGER NOT NULL,
buyer_agent_id INTEGER,
sale_price REAL NOT NULL,
sale_date TEXT NOT NULL,
seller_agent_commission REAL NOT NULL,
buyer_agent_commission REAL,
FOREIGN KEY (house_id) REFERENCES houses(house_id),
FOREIGN KEY (seller_agent_id) REFERENCES agents(agent_id),
FOREIGN KEY (buyer_id) REFERENCES buyers(buyer_id),
FOREIGN KEY (buyer_agent_id) REFERENCES agents(agent_id)
);
INSERT INTO viewings (house_id, person_id, viewing_date)
VALUES
(1, 1, '2023-02-20'),
(1, 2, '2023-02-22'),
(2, 3, '2023-04-10'),
(2, 4, '2023-04-15'),
(3, 5, '2023-02-15'),
(3, 6, '2023-02-25'),
(4, 7, '2023-05-05'),
(4, 8, '2023-05-10'),
(5, 9, '2023-06-01'),
(5, 10, '2023-06-05'),
(6, 11, '2023-07-15'),
(6, 12, '2023-07-20'),
(7, 13, '2023-09-10'),
(7, 14, '2023-09-15'),
(8, 15, '2023-11-05'),
(8, 16, '2023-11-10'),
(9, 17, '2023-01-20'),
(9, 18, '2023-01-25'),
(10, 19, '2023-03-10'),
(10, 20, '2023-03-15'),
(11, 21, '2023-04-20'),
(11, 22, '2023-04-25'),
(12, 23, '2023-06-10'),
(12, 24, '2023-06-15'),
(13, 25, '2023-07-20'),
(13, 1, '2023-07-25'),
(14, 2, '2023-09-05'),
(14, 3, '2023-09-10'),
(15, 4, '2023-11-15'),
(15, 5, '2023-11-20'),
(16, 6, '2023-01-10'),
(16, 7, '2023-01-15'),
(17, 8, '2023-02-25'),
(17, 9, '2023-03-01'),
(18, 10, '2023-04-15'),
(18, 11, '2023-04-20'),
(19, 12, '2023-06-05'),
(19, 13, '2023-06-10'),
(20, 14, '2023-07-25'),
(20, 15, '2023-08-01'),
(21, 16, '2023-09-15'),
(21, 17, '2023-09-20'),
(22, 18, '2023-11-05'),
(22, 19, '2023-11-10'),
(23, 20, '2023-01-10'),
(23, 21, '2023-01-15'),
(24, 22, '2023-02-20'),
(24, 23, '2023-02-25'),
(25, 24, '2023-04-10'),
(25, 25, '2023-04-15');
INSERT INTO agencies (name, email, operating_states, start_year, sale_commission)
VALUES
('California Dream Properties', 'california.dreamprops@email.com', 'California', 2015, 0.06),
('Evergreen Realty', 'evergreen.realty@email.com', 'Washington', 2018, 0.055),
('Lone Star Brokers', 'lonestar.brokers@email.com', 'Texas', 2020, 0.065),
('Golden State Agency', 'golden.state.agency@email.com', 'California', 2016, 0.058),
('Pike Place Properties', 'pike.place.props@email.com', 'Washington', 2019, 0.062),
('Texas Home Finders', 'texas.home.finders@email.com', 'Texas', 2017, 0.057),
('West Coast Living', 'westcoast.living@email.com', 'California, Washington', 2014, 0.063),
('Southwest Realty Group', 'southwest.realty@email.com', 'Texas, California', 2015, 0.059);
INSERT INTO agents (name, email, phone, license_number, agency_id)
VALUES
('Emma Johnson', 'emma.johnson@email.com', '+1 (123) 456-7890', 'LIC-1001', 1),
('Michael Brown', 'michael.brown@email.com', '+1 (987) 654-3210', 'LIC-1002', 1),
('Sophia Williams', 'sophia.williams@email.com', '+1 (555) 123-4567', 'LIC-1003', 2),
('Ethan Davis', 'ethan.davis@email.com', '+1 (111) 222-3333', 'LIC-1004', 2),
('Olivia Taylor', 'olivia.taylor@email.com', '+1 (555) 555-5555', 'LIC-1005', 3),
('Benjamin Moore', 'benjamin.moore@email.com', '+1 (987) 777-7777', 'LIC-1006', 3),
('Avery Wilson', 'avery.wilson@email.com', '+1 (555) 666-6666', 'LIC-1007', 4),
('Isabella Martinez', 'isabella.martinez@email.com', '+1 (222) 222-2222', 'LIC-1008', 4),
('Lucas Anderson', 'lucas.anderson@email.com', '+1 (888) 888-8888', 'LIC-1009', 5),
('Mia Wilson', 'mia.wilson@email.com', '+1 (777) 777-7777', 'LIC-1010', 5),
('Alexander Clark', 'alexander.clark@email.com', '+1 (666) 666-6666', 'LIC-1011', 6),
('Emily Johnson', 'emily.johnson@email.com', '+1 (555) 888-8888', 'LIC-1012', 6),
('William Miller', 'william.miller@email.com', '+1 (444) 444-4444', 'LIC-1013', 7),
('Sophia Young', 'sophia.young@email.com', '+1 (333) 333-3333', 'LIC-1014', 7),
('David Lee', 'david.lee@email.com', '+1 (222) 111-1111', 'LIC-1015', 8),
('Charlotte Harris', 'charlotte.harris@email.com', '+1 (999) 999-9999', 'LIC-1016', 8),
('Gavin Thompson', 'gavin.thompson@email.com', '+1 (555) 999-9999', 'LIC-1017', 1),
('Liam Wilson', 'liam.wilson@email.com', '+1 (111) 555-1111', 'LIC-1018', 3),
('Aria Martinez', 'aria.martinez@email.com', '+1 (222) 666-2222', 'LIC-1019', 5),
('Noah Anderson', 'noah.anderson@email.com', '+1 (444) 888-4444', 'LIC-1020', 7);
INSERT INTO houses (address, city, state, zip_code, listing_price, listing_date, status)
VALUES
('123 Main St', 'Los Angeles', 'California', '90001', 550000, '2023-01-15', 'Available'),
('456 Pine Ave', 'Seattle', 'Washington', '98101', 720000, '2023-03-20', 'Under Contract'),
('789 Elm Rd', 'Houston', 'Texas', '77001', 480000, '2023-02-10', 'Sold'),
('101 Maple Ln', 'San Diego', 'California', '92101', 650000, '2023-04-05', 'Available'),
('202 Oak St', 'Spokane', 'Washington', '99201', 520000, '2023-05-12', 'Under Contract'),
('303 Birch Ave', 'Dallas', 'Texas', '75201', 850000, '2023-06-25', 'Sold'),
('404 Cedar Dr', 'San Francisco', 'California', '94101', 1200000, '2023-07-10', 'Available'),
('505 Willow St', 'Tacoma', 'Washington', '98401', 410000, '2023-08-02', 'Under Contract'),
('606 Poplar Ave', 'Austin', 'Texas', '78701', 780000, '2023-09-15', 'Sold'),
('707 Cypress Ln', 'Sacramento', 'California', '95814', 580000, '2023-10-05', 'Available'),
('808 Ash St', 'Everett', 'Washington', '98201', 630000, '2023-11-20', 'Under Contract'),
('909 Chestnut Dr', 'San Antonio', 'Texas', '78201', 490000, '2023-12-08', 'Sold'),
('110 Walnut Ave', 'Santa Barbara', 'California', '93101', 950000, '2023-01-25', 'Available'),
('212 Beech St', 'Vancouver', 'Washington', '98660', 560000, '2023-02-12', 'Under Contract'),
('313 Juniper Rd', 'El Paso', 'Texas', '79901', 420000, '2023-03-05', 'Sold'),
('414 Laurel Ln', 'Fresno', 'California', '93701', 510000, '2023-04-18', 'Available'),
('515 Holly St', 'Bellevue', 'Washington', '98004', 790000, '2023-05-05', 'Under Contract'),
('616 Magnolia Ave', 'Fort Worth', 'Texas', '76101', 820000, '2023-06-20', 'Sold'),
('717 Lilac Dr', 'Long Beach', 'California', '90801', 680000, '2023-07-08', 'Available'),
('818 Iris Ln', 'Kent', 'Washington', '98030', 590000, '2023-08-25', 'Under Contract'),
('919 Orchid St', 'Corpus Christi', 'Texas', '78401', 460000, '2023-09-10', 'Sold'),
('100 Rose Ave', 'Oakland', 'California', '94601', 750000, '2023-10-02', 'Available'),
('201 Daisy St', 'Bellingham', 'Washington', '98225', 610000, '2023-11-15', 'Under Contract'),
('302 Tulip Ave', 'San Jose', 'California', '95101', 980000, '2023-12-05', 'Available'),
('403 Lily Ln', 'Renton', 'Washington', '98055', 530000, '2023-01-20', 'Under Contract'),
('504 Violet Dr', 'Plano', 'Texas', '75023', 710000, '2023-02-08', 'Sold');
INSERT INTO house_features (house_id, feature_type, description)
VALUES
(1, 'Bedrooms', '3 spacious bedrooms with ample closet space'),
(1, 'Bathrooms', '2 full bathrooms with modern fixtures'),
(1, 'Kitchen', 'Renovated kitchen with granite countertops'),
(2, 'Bedrooms', '2 cozy bedrooms with natural lighting'),
(2, 'Bathrooms', '2.5 bathrooms, including a primary en-suite'),
(2, 'View', 'Stunning city skyline view from the balcony'),
(3, 'Bedrooms', '4 generous-sized bedrooms'),
(3, 'Outdoor Space', 'Spacious backyard with a patio'),
(3, 'Garage', 'Attached 2-car garage'),
(4, 'Bedrooms', '2 bedrooms plus a den/office space'),
(4, 'Kitchen', 'Gourmet kitchen with stainless steel appliances'),
(4, 'Flooring', 'Beautiful hardwood floors throughout'),
(5, 'Bedrooms', '3 bedrooms, including a primary suite with a walk-in closet'),
(5, 'Bathrooms', '2.5 bathrooms with custom tile work'),
(5, 'Fireplace', 'Cozy gas fireplace in the living room'),
(6, 'Bedrooms', '5 luxurious bedrooms, each with its own charm'),
(6, 'Outdoor Space', 'Landscaped garden with a koi pond'),
(6, 'Smart Home', 'Smart home features, including lighting and climate control'),
(7, 'Bedrooms', '2 bedrooms with breathtaking ocean views'),
(7, 'Kitchen', 'Chef''s kitchen with a large island and wine fridge'),
(7, 'Balcony', 'Spacious balcony overlooking the city'),
(8, 'Bedrooms', '3 cozy bedrooms with soft carpeting'),
(8, 'Bathrooms', '2 full bathrooms with double vanities'),
(8, 'Laundry', 'In-unit laundry room'),
(9, 'Bedrooms', '4 bedrooms, perfect for a growing family'),
(9, 'Home Office', 'Dedicated home office space'),
(9, 'Energy Efficient', 'Energy-efficient appliances and solar panels'),
(10, 'Bedrooms', '2 spacious bedrooms with ample natural light'),
(10, 'Outdoor Space', 'Private backyard with a deck for entertaining'),
(10, 'Flooring', 'Refinished hardwood floors'),
(11, 'Bedrooms', '3 bedrooms, including a primary retreat with a spa-like en-suite'),
(11, 'Kitchen', 'Open-concept kitchen with quartz countertops'),
(11, 'Garage', 'Detached 2-car garage with additional storage'),
(12, 'Bedrooms', '2 cozy bedrooms, perfect for a small family or roommates'),
(12, 'Bathrooms', '1.5 bathrooms with updated fixtures'),
(12, 'Location', 'Conveniently located near public transportation and amenities'),
(13, 'Bedrooms', '4 generously-sized bedrooms, each with its own unique charm'),
(13, 'Outdoor Space', 'Large front porch and a private backyard'),
(13, 'Smart Home', 'Smart home capabilities, including voice control'),
(14, 'Bedrooms', '3 bright and airy bedrooms'),
(14, 'Kitchen', 'Gourmet kitchen with a gas range and custom cabinetry'),
(14, 'Flooring', 'Elegant hardwood floors and plush carpeting in bedrooms'),
(15, 'Bedrooms', '2 bedrooms, ideal for a couple or small family'),
(15, 'Bathrooms', '2 full bathrooms, one with a soaking tub'),
(15, 'Laundry', 'In-unit washer and dryer'),
(16, 'Bedrooms', '4 spacious bedrooms, offering privacy and comfort'),
(16, 'Outdoor Space', 'Beautifully landscaped backyard with a pergola'),
(16, 'Energy Efficient', 'Energy-efficient windows and LED lighting throughout'),
(17, 'Bedrooms', '3 cozy bedrooms, perfect for a peaceful retreat'),
(17, 'Kitchen', 'Updated kitchen with new appliances and granite countertops'),
(17, 'Flooring', 'Refinished hardwood floors and new carpet in bedrooms'),
(18, 'Bedrooms', '2 bright and spacious bedrooms with ample storage'),
(18, 'Bathrooms', '2 full bathrooms featuring designer touches'),
(18, 'Location', 'Prime location within walking distance to parks and restaurants'),
(19, 'Bedrooms', '4 generously-sized bedrooms, ideal for a large family'),
(19, 'Home Office', 'Dedicated home office with built-in shelves'),
(19, 'Garage', 'Oversized 2-car garage with additional workshop space'),
(20, 'Bedrooms', '3 comfortable bedrooms, each with its own personality'),
(20, 'Outdoor Space', 'Private backyard with a fire pit and seating area'),
(20, 'Smart Home', 'Smart home system for added convenience and security'),
(21, 'Bedrooms', '2 stylish bedrooms with modern finishes'),
(21, 'Kitchen', 'Sleek and functional kitchen with an island'),
(21, 'Flooring', 'Contemporary hardwood floors and tile in wet areas'),
(22, 'Bedrooms', '3 spacious bedrooms, perfect for a growing family or roommates'),
(22, 'Bathrooms', '2.5 bathrooms, including a primary en-suite with a soaking tub'),
(22, 'Laundry', 'Dedicated laundry room with storage space'),
(23, 'Bedrooms', '4 well-appointed bedrooms, each with its own charm'),
(23, 'Outdoor Space', 'Landscaped front and backyard with mature trees'),
(23, 'Energy Efficient', 'Energy-efficient appliances and solar panels for reduced utility costs'),
(24, 'Bedrooms', '2 cozy bedrooms, ideal for a couple or roommates'),
(24, 'Kitchen', 'Updated kitchen with stainless steel appliances and granite countertops'),
(24, 'Location', 'Conveniently located near schools, shops, and public transportation'),
(25, 'Bedrooms', '3 bright and airy bedrooms, perfect for a peaceful retreat'),
(25, 'Bathrooms', '2 full bathrooms with custom tile work and designer fixtures'),
(25, 'Smart Home', 'Smart home features, including voice control and automated lighting');
INSERT INTO people (name, age, email, phone)
VALUES
('Aria Williams', 32, 'aria.williams@email.com', '+1 (555) 666-7777'),
('Ethan Miller', 45, 'ethan.miller@email.com', '+1 (123) 444-5555'),
('Emma Johnson', 28, 'emma.johnson123@email.com', '+1 (987) 666-7777'),
('Alexander Brown', 52, 'alexander.brown@email.com', '+1 (555) 888-9999'),
('Sophia Martinez', 36, 'sophia.martinez@email.com', '+1 (222) 111-2222'),
('Benjamin Lee', 24, 'benjamin.lee@email.com', '+1 (888) 999-8888'),
('Isabella Moore', 30, 'isabella.moore@email.com', '+1 (777) 666-5555'),
('Michael Wilson', 40, 'michael.wilson@email.com', '+1 (666) 555-4444'),
('Emily Davis', 22, 'emily.davis@email.com', '+1 (555) 999-8888'),
('William Clark', 58, 'william.clark@email.com', '+1 (444) 777-6666'),
('Olivia Anderson', 26, 'olivia.anderson@email.com', '+1 (333) 888-7777'),
('Lucas Taylor', 34, 'lucas.taylor@email.com', '+1 (222) 999-6666'),
('Avery Thompson', 48, 'avery.thompson@email.com', '+1 (111) 888-5555'),
('David Johnson', 50, 'david.johnson@email.com', '+1 (999) 777-6666'),
('Charlotte Wilson', 42, 'charlotte.wilson@email.com', '+1 (555) 444-9999'),
('Gavin Young', 20, 'gavin.young@email.com', '+1 (222) 888-7777'),
('Liam Smith', 38, 'liam.smith@email.com', '+1 (777) 999-6666'),
('Mia Lee', 26, 'mia.lee@email.com', '+1 (666) 888-5555'),
('Noah Harris', 44, 'noah.harris@email.com', '+1 (555) 777-9999'),
('Sophia Anderson', 32, 'sophia.anderson@email.com', '+1 (444) 666-8888'),
('William Miller', 56, 'william.millerjr@email.com', '+1 (333) 999-7777'),
('Emma Johnson', 28, 'emma.johnson@email.net', '+1 (222) 555-6666'),
('Ethan Davis', 40, 'ethan.d@email.com', '+1 (888) 777-9999'),
('Olivia Wilson', 36, 'olivia.w@email.com', '+1 (777) 888-6666'),
('Benjamin Moore', 24, 'ben.moore@email.com', '+1 (666) 999-5555'),
('Alexander Smith', 52, 'alex.smith@email.com', '+1 (555) 666-8888'),
('Isabella Lee', 30, 'isabella.l@email.com', '+1 (444) 777-9999'),
('Michael Johnson', 46, 'michael.j@email.com', '+1 (333) 888-6666'),
('Emily Brown', 22, 'emily.brown@email.com', '+1 (222) 999-7777'),
('William Clark', 58, 'william.clark@email.org', '+1 (111) 666-5555'),
('Aria Martinez', 34, 'aria.m@email.com', '+1 (987) 654-3210'),
('David Wilson', 48, 'david.wilson@email.com', '+1 (555) 123-4567'),
('Sophia Miller', 36, 'sophia.miller@email.com', '+1 (222) 222-2222'),
('Ethan Anderson', 42, 'ethan.anderson@email.com', '+1 (888) 888-8888'),
('Olivia Thompson', 28, 'olivia.thompson@email.com', '+1 (777) 777-7777'),
('Benjamin Johnson', 24, 'ben.johnson@email.com', '+1 (666) 666-6666'),
('Alexander Lee', 54, 'alexander.lee@email.com', '+1 (555) 555-5555'),
('Isabella Smith', 32, 'isabella.smith@email.com', '+1 (444) 444-4444'),
('Michael Brown', 40, 'michaelb@email.com', '+1 (333) 333-3333'),
('Emma Wilson', 28, 'emma.wilson@email.com', '+1 (222) 222-1111'),
('William Johnson', 56, 'williamj@email.com', '+1 (111) 111-1111'),
('Olivia Miller', 30, 'olivia.miller@email.com', '+1 (999) 999-9999'),
('Benjamin Anderson', 26, 'ben.anderson@email.com', '+1 (888) 888-5555'),
('Alexander Smith', 50, 'alex.smithsr@email.com', '+1 (777) 777-7777'),
('Isabella Martinez', 34, 'isabella.martinez@email.com', '+1 (666) 666-6666'),
('Michael Johnson', 44, 'michael.johnson@email.co.uk', '+1 (555) 555-5550'),
('Emma Brown', 26, 'emma.brown@email.com', '+1 (444) 444-4444'),
('William Clark', 52, 'william.clark2@email.com', '+1 (333) 333-3330'),
('Aria Lee', 32, 'aria.lee@email.com', '+1 (222) 222-2220'),
('Ethan Wilson', 40, 'ethan.wilson@email.com', '+1 (111) 111-1110');
INSERT INTO viewings (house_id, person_id, viewing_date)
VALUES
(1, 1, '2023-02-20'),
(1, 2, '2023-02-22'),
(2, 3, '2023-04-10'),
(2, 4, '2023-04-15'),
(3, 5, '2023-02-15'),
(3, 6, '2023-02-25'),
(4, 7, '2023-05-05'),
(4, 8, '2023-05-10'),
(5, 9, '2023-06-01'),
(5, 10, '2023-06-05'),
(6, 11, '2023-07-15'),
(6, 12, '2023-07-20'),
(7, 13, '2023-09-10'),
(7, 14, '2023-09-15'),
(8, 15, '2023-11-05'),
(8, 16, '2023-11-10'),
(9, 17, '2023-01-20'),
(9, 18, '2023-01-25'),
(10, 19, '2023-03-10'),
(10, 20, '2023-03-15'),
(11, 21, '2023-04-20'),
(11, 22, '2023-04-25'),
(12, 23, '2023-06-10'),
(12, 24, '2023-06-15'),
(13, 25, '2023-07-20'),
(13, 1, '2023-07-25'),
(14, 2, '2023-09-05'),
(14, 3, '2023-09-10'),
(15, 4, '2023-11-15'),
(15, 5, '2023-11-20'),
(16, 6, '2023-01-10'),
(16, 7, '2023-01-15'),
(17, 8, '2023-02-25'),
(17, 9, '2023-03-01'),
(18, 10, '2023-04-15'),
(18, 11, '2023-04-20'),
(19, 12, '2023-06-05'),
(19, 13, '2023-06-10'),
(20, 14, '2023-07-25'),
(20, 15, '2023-08-01');
INSERT INTO sales (house_id, seller_agent_id, buyer_id, buyer_agent_id, sale_price, sale_date, seller_agent_commission, buyer_agent_commission)
VALUES
(3, 1, 2, 3, 475000, '2023-03-10', 0.028, 0.032),
(6, 4, 5, 6, 835000, '2023-07-05', 0.0354, 0.03),
(9, 7, 8, 9, 485000, '2023-10-15', 0.02975, 0.028),
(12, 10, 11, 12, 415000, '2023-07-10', 0.0325, 0.034),
(15, 13, 14, NULL, 795000, '2023-12-20', 0.036, 0),
(18, 16, 17, 18, 770000, '2023-10-30', 0.0342, 0.031),
(21, 19, 20, 21, 600000, '2023-09-15', 0.0378, 0.036),
(24, 22, 23, 24, 450000, '2023-04-10', 0.027, 0.029),
(1, 2, 25, 1, 540000, '2023-05-20', 0.0324, 0.033),
(4, 3, 1, NULL, 640000, '2023-06-15', 0.0344, 0),
(7, 5, 3, 4, 1150000, '2023-11-10', 0.069, 0.035),
(10, 6, 5, 7, 620000, '2023-02-05', 0.0356, 0.0296),
(13, 8, 9, 10, 488000, '2023-08-20', 0.02928, 0.0308),
(16, 11, 12, 13, 810000, '2023-07-01', 0.04692, 0.0325),
(19, 14, 15, 16, 480000, '2023-06-30', 0.0288, 0.0264),
(22, 17, 18, 19, 525000, '2023-11-25', 0.0315, 0.0285),
(2, 1, 4, 2, 710000, '2023-05-10', 0.04045, 0.0385),
(5, 2, 6, 3, 510000, '2023-07-15', 0.0306, 0.0285),
(8, 4, 7, 5, 775000, '2023-10-05', 0.04465, 0.0345),
(11, 3, 8, 6, 570000, '2023-03-25', 0.03315, 0.0294),
(14, 5, 9, 7, 740000, '2023-12-10', 0.04272, 0.0315),
(17, 6, 10, 8, 670000, '2023-09-20', 0.04158, 0.0308),
(20, 7, 11, 9, 890000, '2023-08-15', 0.05162, 0.0342);
-- Profit margin for agencies
-- SELECT
-- a.agency_id,
-- a.name AS agency_name,
-- SUM(s.seller_agent_commission + COALESCE(s.buyer_agent_commission, 0)) * 0.70 AS net_profit
-- FROM
-- agencies a
-- JOIN
-- agents ag ON a.agency_id = ag.agency_id
-- LEFT JOIN
-- sales s ON s.seller_agent_id = ag.agent_id OR s.buyer_agent_id = ag.agent_id
-- GROUP BY
-- a.agency_id
-- ORDER BY
-- net_profit DESC;
-- Most optimal listing time
-- SELECT
-- STRFTIME('%m', listing_date) AS listing_month,
-- AVG(julianday(s.sale_date) - julianday(h.listing_date)) AS avg_days_on_market,
-- AVG(s.sale_price) AS avg_sale_price
-- FROM
-- houses h
-- JOIN
-- sales s ON h.house_id = s.house_id
-- GROUP BY
-- listing_month
-- ORDER BY
-- avg_sale_price DESC, avg_days_on_market ASC
-- LIMIT 1;
-- Kitchens vs bathrooms
-- WITH Bathroom_Featured_Houses AS (
-- SELECT h.house_id
-- FROM house_features hf
-- JOIN houses h ON hf.house_id = h.house_id
-- WHERE hf.feature_type = 'Bathrooms'
-- ),
-- Kitchen_Featured_Houses AS (
-- SELECT h.house_id
-- FROM house_features hf
-- JOIN houses h ON hf.house_id = h.house_id
-- WHERE hf.feature_type = 'Kitchen'
-- ),
-- Bathroom_Avg_Price AS (
-- SELECT AVG(s.sale_price) AS avg_bathroom_price
-- FROM sales s
-- JOIN Bathroom_Featured_Houses bfh ON s.house_id = bfh.house_id
-- ),
-- Kitchen_Avg_Price AS (
-- SELECT AVG(s.sale_price) AS avg_kitchen_price
-- FROM sales s
-- JOIN Kitchen_Featured_Houses kfh ON s.house_id = kfh.house_id
-- )
-- SELECT
-- CASE
-- WHEN (SELECT avg_bathroom_price FROM Bathroom_Avg_Price) > (SELECT avg_kitchen_price FROM Kitchen_Avg_Price) THEN 'Yes'
-- ELSE 'No'
-- END AS Bathroom_Higher_Than_Kitchen
-- SELECT
-- age_group,
-- AVG(listing_price) AS avg_listing_price,
-- MAX(common_feature) AS most_common_feature
-- FROM (
-- SELECT
-- p.person_id,
-- s.house_id,
-- h.listing_price,
-- CASE
-- WHEN p.age BETWEEN 18 AND 30 THEN '18-30'
-- WHEN p.age BETWEEN 31 AND 40 THEN '31-40'
-- WHEN p.age BETWEEN 41 AND 50 THEN '41-50'
-- ELSE '51+'
-- END AS age_group,
-- hf.feature_type AS common_feature,
-- ROW_NUMBER() OVER(PARTITION BY p.person_id ORDER BY COUNT(hf.feature_type) DESC) AS rn
-- FROM
-- people p
-- JOIN sales s ON p.person_id = s.buyer_id
-- JOIN houses h ON s.house_id = h.house_id
-- JOIN house_features hf ON h.house_id = hf.house_id
-- GROUP BY p.person_id, hf.feature_type
-- ) sub
-- WHERE rn = 1
-- GROUP BY age_group
-- ORDER BY age_group;
WITH RankedSales AS (
SELECT
s.sale_price,
s.seller_agent_id,
h.city,
(s.sale_price * s.seller_agent_commission) AS CommissionAmount,
RANK() OVER (PARTITION BY h.city ORDER BY s.sale_price DESC) AS SaleRank
FROM sales s
JOIN houses h ON s.house_id = h.house_id
),
TotalCommissionByAgentAndCity AS (
SELECT
s.seller_agent_id,
h.city,
SUM(s.sale_price * s.seller_agent_commission) AS TotalCommission
FROM sales s
JOIN houses h ON s.house_id = h.house_id
GROUP BY s.seller_agent_id, h.city
),
TopSales AS (
SELECT
rs.seller_agent_id,
rs.city,
rs.sale_price AS MostExpensiveSale,
tcbc.TotalCommission
FROM RankedSales rs
JOIN TotalCommissionByAgentAndCity tcbc ON rs.seller_agent_id = tcbc.seller_agent_id AND rs.city = tcbc.city
WHERE rs.SaleRank <= 2
)
SELECT
a.name AS AgentName,
ts.city,
ts.MostExpensiveSale,
ts.TotalCommission
FROM TopSales ts
JOIN agents a ON ts.seller_agent_id = a.agent_id
ORDER BY ts.city, ts.MostExpensiveSale DESC;
To embed this program on your website, copy the following code and paste it into your website's HTML: