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)
Writing Basic SQL Queries¶ As part of this section we will primarily focus on writing basic queries. Standard Transformations Overview of Data Model Define Problem Statement – Daily Product Revenue Preparing Tables Selecting or Projecting Data Filtering Data Joining Tables – Inner Joining Tables – Outer Performing Aggregations Sorting Data Solution – Daily Product Revenue …
