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 …
Inserting Data¶ Let us see how to insert the data into the table. We need to use INSERT clause to insert the data. Here is the sample syntax. INSERT INTO <table_name> (col1, col2, col3) VALUES (val1, val2, val3) If we don’t pass columns after table name then we need to specify values for all the …
