The Olist Database

Note: For a better view of the exercise and solution panel, please close the left sidebar.

Introduction

Welcome! This is a Brazilian ecommerce public dataset of orders made at Olist Store. The dataset has information of 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil. Its features allows viewing an order from multiple dimensions: from order status, price, payment and freight performance to customer location, product attributes and finally reviews written by customers. We also released a geolocation dataset that relates Brazilian zip codes to lat/lng coordinates.

This is real commercial data, it has been anonymised, and references to the companies and partners in the review text have been replaced with the names of Game of Thrones great houses.

Entity Relationship Diagram

SQLite Date Formatting

In this exercise, you will query data from an SQLite database. Unlike other DBMS that support the to_char() function for formatting dates, in SQLite, we use the strftime() function.

Examples:

-- Other DBMS
SELECT TO_CHAR(order_date, 'YYYY') AS "Year"
FROM orders;

-- SQLite
SELECT strftime('%Y', order_date) AS "Year"
FROM orders;

Note: SQLite offers a wide range of utility functions to help you work with time and date data effectively. You are encouraged to explore and use them as needed. For more details, refer to this tutorial: SQLite strftime() function