Analytics

INTRODUCTION

Welcome to our demonstration of Tacnode's analytical query capabilities. We'll be using SQL in pgAdmin for this demonstration.

In the business world, we frequently encounter situations where data analysis is essential. We can optimize business decisions by analyzing sales data, marketing data, and consumer data. For example, by observing guest behavior and preferences, we gain consumer insights. With these insights, we can then employ precision marketing (a targeted strategy focused on retaining, cross-selling, and upselling existing customers).

From start-ups to established corporations, businesses of all sizes rely on data analysis to foster growth. When performing data analysis, we're often working with data on a large scale, and the queries tend to be quite complex. Generally, we'll need query techniques like multi-table JOINs and aggregation operations.

Next, we'll begin our demonstration of Tacnode's analytical capabilities using data from the Tacnode Playground.

SCENARIO 1: MULTI-TABLE JOINS

For our first scenario, we need to search for the Top 10 listings with the most reviews, and also display their locations. In this BnB application, we've modelled the listings using one table, the locations using another table, and the reviews in a third table. Since different information is stored in different tables, we need to perform JOIN operations to satisfy the query criteria.

First, we start with the listings table, which contains information about different properties listed on the site. We join the listings table with the review_count subquery, where we calculate the number of reviews for each listing, and then sort the listings by review count to find the Top 10 listings.

Lastly, we can obtain the relevant location information for each listing by joining the hosts table with the listings table. And now, we get the final result for this query.

We can see that the query returns the name and location of the Top 10 listings with the most reviews, along with their respective review_count s.

As we've just witnessed, Tacnode handles multi-table JOINs quite well, with exceptionally fast query execution.

SCENARIO 2: FULL-TEXT SEARCH

For our second scenario, we have an analytical query with full-text search. Let's say we need to find all listings — containing the keyword "condo" — that are open for reservations from January 1, 2024 to January 3, 2024, and then group them by room type. First, we need to filter out the listings from the calendar table with available dates within the specified time range. We perform an inner join between the listings table and the calendar table to look for availability data tied to each listing. Since the calendar table contains 500 million records, this is quite a challenging task for the query engine.

Within these available listings, we also need to filter out the listings containing the keyword "condo" using full-text search. Lastly, we group the listings by room type and get our final result.

Through this query, we see that Tacnode handles full-text search and GROUP BY clauses quite well, with fast and efficient query execution. From the results, we see that most of the available listings are entire homes/apartments, followed by private rooms.

And with that, we conclude this demonstration of Tacnode's analytical query capabilities. Thank you for tuning in.

QUERIES

SCENARIO 1: Multi-table JOINs — search for the Top 10 listings with the most reviews (obtain locations + review counts)

select
  name,
  location,
  review_count
from
  listings
join (
  select
    count(*) as review_count,
    listing_id
  from
    reviews
  group by
    listing_id
  order by
    review_count desc
  limit
    10
) as review_top on listings.id = review_top.listing_id
join hosts on listings.host_id = hosts.id
order by
  review_count desc;

Scenario 2: Omni Search — find all listings (containing the keyword "condo") open for reservations from January 1, 2024 to January 3, 2024 and group the results by room type

select
  room_type,
  count(1) as host_roomtype_count
from
  listings
inner join
  calendar on listings.id = calendar.listing_id
where
  keys @@ to_tsquery('condo')
  and calendar.available
  and date between '2024-01-01' and '2024-01-03'
group by
  room_type;