Joining Tables – Inner¶ Let us understand how to join data from multiple tables. We will primarily focus on ANSI style join (JOIN with ON). There are different types of joins. INNER JOIN – Get all the records from both the datasets which satisfies JOIN condition. OUTER JOIN – We will get into the details …
Selecting or Projecting Data¶ Let us understand different aspects of projecting data. We primarily using SELECT to project the data. We can project all columns using * or some columns using column names. We can provide aliases to a column or expression using AS in SELECT clause. DISTINCT can be used to get the distinct …
Preparing Tables¶ Let us prepare retail tables to come up with the solution for the problem statement. Ensure that we have required database and user for retail data. We might provide the database as part of our labs. Here are the instructions to use psql for setting up the required tables as part of self …
Define Problem Statement – Daily Product Revenue¶ Let us try to get daily product revenue using retail tables. daily is derived from orders.order_date. product has to be derived from products.product_name. revenue has to be derived from order_items.order_item_subtotal. We need to join all the 3 tables, then group by order_date, product_id as well as product_name to …
Overview of Data Model¶ We will be using retail data model for this section. It contains 6 tables. Table list orders order_items products categories departments customers orders and order_items are transactional tables. products, categories and departments are non transactional tables which have data related to product catalog. customers is a non transactional table which have …
Standard Transformations¶ Here are some of the transformations we typically perform on regular basis. Projection of data Filtering data Performing Aggregations Joins Sorting Ranking (will be covered as part of advanced queries)
