There’s something about SQL and PIVOTs: Everyone wants to PIVOT their results, but there never seems to be an easy way for it. Fortunately, Snowflake users have a native way to perform pivots in SQL, with the function PIVOT():

select *
from (
  select * from table(result_scan(last_query_id(-1)))
)
pivot(max(pivot_value)
for pivot_column in ('Spider-Man', 'Wonder Woman', 'Iron Man'))

During 2020, more than 500 companies in the data and analytics space were acquired all over the world. Can you guess the top three cities where these companies are based?

Number three is London, number two is New York. And number one is San Francisco, where 33 companies in the data analytics space were acquired in 2020.

2020 has been a year full of surprises — one of them being that I left Google and joined Snowflake. Last year I did my first run for #AdventOfCode with SQL, and this year the challenge is helping me get acquainted with a different SQL syntax than the one I was used to with BigQuery.

I’ll leave some notes here on my major discoveries and learnings — while developing my new Snowflake expertise.

Find the full code on Github for these solutions..

Day 1: Multiple joins

The first problem was pretty easy to solve with SQL.

Part 1 took only a cross join…

Join us Dec 14 to go deeper:

Video transcription:

Let me show you how cool is to analyze live data with Snowflake and ThoughtSpot.

Last year, ThoughtSpot released a video with a cool demo, especially made for fantasy football players. They loaded NFL data into ThoughtSpot and they showed how easy is to get insights and visualizations through their search bar without having to write SQL. This year, they improved this demo by moving the data out of ThoughtSpot and into Snowflake. …

Caveat: If you are in a marketing organization driving marketing campaigns, you should totally use UTM codes. For example, this post from Lee Hurst begs marketers to start using more UTM codes.

So why shouldn’t you use UTM codes?

Human filtering

Every time we dive into social media (email, reddit, Twitter, LinkedIn, Facebook, …) our brains drown in a sea of information overload. We are in this site to find interesting links, but our brains have had to develop quick strategies to separate real content from ads.

Which of these links would you rather click on?

1. https://www.elisbwbar.com/
2. https://www.elisbwbar.com/?utm_source=gmb&utm_medium=organic&utm_content=main&utm_campaign=listing
3. https://bit.ly/3fQQMAW

The answer is…

I’m a big fan of dbt — an open source project that helps me build data pipelines around BigQuery using only SQL.

Get started with BigQuery and dbt

There’s a lot already writen about Bigquery and dbt. For example, there’s this official tutorial to set up dbt with BigQuery, with a lot more details than I do here (thanks Claire Carroll). The goal of this post is to share with you some GCP secrets to make the installation as easy as possible.

Step 1: Create a free Google Cloud account

Good news: You don’t need a credit card…

Finding the top Stack Overflow tags of 2023

Let’s start by visualizing the top Stack Overflow tags on June 2020:

Quick pivots

Before going any further, let me show you how to call fhoffa.x.pivot() to easily pivot any table, creating a new one with the results:

CALL fhoffa.x.pivot(
  'bigquery-public-data.iowa_liquor_sales.sales' # source table
  , 'fh-bigquery.temp.test' # destination table
  , ['date'] # row_ids
  , 'store_number' # pivot_col_name
  , 'sale_dollars' # pivot_col_value
  , 30 # max_columns
  , 'SUM' # aggregation
  , '' # optional_limit
);

That’s it. Let’s see what’s happening here. The original table has one row per sale (invoice), including the date of the transaction, the store number, and the sale amount: