Skip to content

Instantly share code, notes, and snippets.

@lenalytics
Created August 1, 2021 11:14
Show Gist options
  • Save lenalytics/3f2a4bab5965c0da41544ee9e9ead0f0 to your computer and use it in GitHub Desktop.
Save lenalytics/3f2a4bab5965c0da41544ee9e9ead0f0 to your computer and use it in GitHub Desktop.
Airbnb - Listing Table
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment