Airbnb - Listing Table

listing.sql

BEGIN;

CREATE TABLE "airdata"."listing"
(
    "id"                                           BIGINT NOT NULL,
    "listing_url"                                  TEXT,
    "scrape_id"                                    BIGINT NOT NULL,
    "last_searched"                                timestamptz,
    "last_scraped"                                 timestamptz,
    "name"                                         TEXT,
    "description"                                  TEXT,
    "neighborhood_overview"                        TEXT,
    "picture_url"                                  TEXT,
    "host_id"                                      BIGINT,
    "host_url"                                     TEXT,
    "host_name"                                    TEXT,
    "host_since"                                   timestamptz,
    "host_location"                                TEXT,
    "host_about"                                   TEXT,
    "host_response_time"                           TEXT,
    "host_response_rate"                           DECIMAL,
    "host_acceptance_rate"                         DECIMAL,
    "host_is_superhost"                            BOOLEAN,
    "host_thumbnail_url"                           TEXT,
    "host_picture_url"                             TEXT,
    "host_neighbourhood"                           TEXT,
    "host_listings_count"                          INTEGER,
    "host_total_listings_count"                    INTEGER,
    "host_verifications"                           TEXT[],
    "host_has_profile_pic"                         BOOLEAN,
    "host_identity_verified"                       BOOLEAN,
    "neighbourhood"                                TEXT,
    "location"                                     POINT,
    "latitude"                                     FLOAT,
    "longitude"                                    FLOAT,
    "property_type"                                TEXT,
    "room_type"                                    TEXT,
    "accommodates"                                 INTEGER,
    "bathrooms"                                    INTEGER,
    "bathrooms_text"                               TEXT,
    "bedrooms"                                     INTEGER,
    "beds"                                         INTEGER,
    "amenities"                                    TEXT[],
    "price"                                        DECIMAL,
    "minimum_nights"                               INTEGER,
    "maximum_nights"                               INTEGER,
    "minimum_minimum_nights"                       INTEGER,
    "maximum_minimum_nights"                       INTEGER,
    "minimum_maximum_nights"                       INTEGER,
    "maximum_maximum_nights"                       INTEGER,
    "minimum_nights_avg_ntm"                       INTEGER,
    "maximum_nights_avg_ntm"                       INTEGER,
    "calendar_updated"                             TEXT,
    "has_availability"                             BOOLEAN,
    "availability_30"                              INTEGER,
    "availability_60"                              INTEGER,
    "availability_90"                              INTEGER,
    "availability_365"                             INTEGER,
    "calendar_last_scraped"                        DATE,
    "number_of_reviews"                            INTEGER,
    "number_of_reviews_ltm"                        INTEGER,
    "number_of_reviews_l30d"                       INTEGER,
    "first_review"                                 DATE,
    "last_review"                                  DATE,
    "review_scores_rating"                         INTEGER,
    "review_scores_accuracy"                       INTEGER,
    "review_scores_cleanliness"                    INTEGER,
    "review_scores_checkin"                        INTEGER,
    "review_scores_communication"                  INTEGER,
    "review_scores_location"                       INTEGER,
    "review_scores_value"                          INTEGER,
    "requires_license"                             BOOLEAN,
    "license"                                      TEXT,
    "instant_bookable"                             BOOLEAN,
    "calculated_host_listings_count"               INTEGER,
    "calculated_host_listings_count_entire_homes"  INTEGER,
    "calculated_host_listings_count_private_rooms" INTEGER,
    "calculated_host_listings_count_shared_rooms"  INTEGER,
    "region_id"                                    BIGINT,
    "region_name"                                  TEXT,
    "region_parent_id"                             BIGINT,
    "region_parent_name"                           TEXT,
    "region_parent_parent_id"                      BIGINT,
    "region_parent_parent_name"                    TEXT,
    "reviews_per_month"                            DECIMAL,
    CONSTRAINT "PK_listing_01" PRIMARY KEY ("id")
);

CREATE INDEX "IDX_airbnb_02" ON "airdata"."listing" ("host_id");
CREATE INDEX "IDX_airbnb_03" ON "airdata"."listing" ("property_type");
CREATE INDEX "IDX_airbnb_04" ON "airdata"."listing" ("room_type");
CREATE INDEX "IDX_airbnb_05" ON "airdata"."listing" ("bedrooms");

COMMIT;