Introduction Data analysts frequently transform data within a database so that it can be…
- User
Introduction Data analysts frequently transform data within a database so that it can be used for analysis and so that the data will be easier for nontechnical users to use and understand. You will emulate such a process in this task by choosing your own business question to analyze, creating tables and queries to use as a business report, and streamlining your analysis by writing your own SQL functions, triggers, and stored procedures. This task defines a report as a collection of data that answers a real-world business question. Your report will have two distinct sections (SQL tables that you will create) that differ in the granularity of the data they present and how directly they support the answering of the business question you choose. The detailed table should contain all data that informs the answer to the question at a very granular level, and the summary table should contain aggregated data that provide a direct answer to the business question.
Requirements To work on this task, use the “Labs on Demand Assessment Environment and DVD Database” found in the Web Links section. In this environment, you will be able to write and test your PostgreSQL code and access the database to complete this task. Plan for and compose the sections of a real-world business report that can be created from the “Labs on Demand Assessment Environment and DVD Database” web link, and demonstrate the functionality of the supporting SQL code by doing the following: A. Summarize one real-world written business report that can be created from the DVD Dataset from the “Labs on Demand Assessment Environment and DVD Database” attachment.
- Identify the specific fields that will be included in the detailed table and the summary table of the report.
- Describe the types of data fields used for the report.
- Identify at least two specific tables from the given dataset that will provide the data necessary for the detailed table section and the summary table section of the report.
- Identify at least one field in the detailed table section that will require a custom transformation with a user-defined function and explain why it should be transformed (e.g., you might translate a field with a value of N to No and Y to Yes).
- Explain the different business uses of the detailed table section and the summary table section of the report.
- Explain how frequently your report should be refreshed to remain relevant to stakeholders. B. Provide original code for function(s) in text format that perform the transformation(s) you identified in part A4. C. Provide original SQL code in a text format that creates the detailed and summary tables to hold your report table sections. D. Provide an original SQL query in a text format that will extract the raw data needed for the detailed section of your report from the source database. E. Provide original SQL code in a text format that creates a trigger on the detailed table of the report that will continually update the summary table as data is added to the detailed table. F. Provide an original stored procedure in a text format that can be used to refresh the data in both the detailed table and summary table. The procedure should clear the contents of the detailed table and summary table and perform the raw data extraction from part D.
- Identify a relevant job scheduling tool that can be used to automate the stored procedure. Rubric The submission accurately and completely summarizes 1 real-world written business report created from the dataset. The submission identifies all of the specific fields that will be included in the detailed table and summary table of the report.
The submission accurately and completely describes the types of data fields that will be used in the report.
The submission identifies at least 2 tables from the given dataset that provide the data necessary for the detailed table section and summary table section of the report.
The submission identifies at least 1 field in the detailed table section of the report that will require a custom transformation and explains why it should be transformed with a user-defined function.
The business uses of the summary and detailed table sections of the report accurately and logically explains how they can be used to improve business.
The explanation of how frequently the report should be refreshed to remain relevant to stakeholders has a reasonable frequency. The explanation is adequate and logical.
The text format code for the function(s) that perform the transformation(s) identified in part A4 produced the expected transformations and all previously identified functions are included in the submission.
The SQL code that creates the detailed and summary tables is correct, complete, and in a text format.
The text-format SQL query provided correctly queries the raw data needed for the detailed section of the report from the source database.
The SQL code in text format successfully creates a trigger on the detailed table of the report that will continually update the summary table as data is added to the detailed table.
The stored procedure in a text format provided refreshes the data in both the detailed table and summary table, and it can clear the contents of the detailed table and summary table and perform the raw data extraction from part D.
A relevant job scheduling tool that can be used to automate the stored procedure is identified. A. Business Report Overview my work so far: Real-World Business Report: Rental Patterns Analysis
The objective of this business report is to analyze rental patterns and identify top-performing films in terms of rental frequency and revenue. This report will assist the management in understanding rental trends and deciding on future inventory acquisitions.
- Fields Included:
-
Detailed Table Fields:
-
rental_id
-
rental_date
-
return_date
-
customer_id
-
film_id
-
title
-
rental_rate
-
total_rental_duration (custom transformation field)
-
Summary Table Fields:
-
film_id
-
title
-
total_rentals
-
total_revenue
- Types of Data Fields:
- Integer: rental_id, customer_id, film_id, total_rentals
- Date: rental_date, return_date
- Text: title
- Numeric: rental_rate, total_revenue
- Interval: total_rental_duration
- Tables Used from Dataset:
- rental
- film
- payment
- Custom Transformation Need:
- Field: total_rental_duration
- Transformation Explanation: This field calculates the length of time a film is rented by subtracting rental_date from return_date. This transformation is necessary to understand rental behavior and usage patterns.
- Business Uses:
- Detailed Table: Used by analysts to perform in-depth analysis on rental patterns, customer behavior, and the performance of individual films.
- Summary Table: Provides management with a quick overview of top-performing films, rental frequency, and revenue, assisting in strategic decision-making.
- Report Refresh Frequency:
- The report should be refreshed weekly to provide up-to-date insights into rental patterns and inventory performance, ensuring timely adjustments can be made if necessary.
B. Function for Data Transformation
CREATE FUNCTION calculate_rental_duration(rental_date DATE, return_date DATE) RETURNS INTERVAL AS BEGIN RETURN return_date - rental_date; END;
LANGUAGE plpgsql;
C. SQL Code to Create Tables
– Create Detailed Table CREATE TABLE detailed_rental_report ( rental_id INTEGER, rental_date DATE, return_date DATE, customer_id INTEGER, film_id INTEGER, title TEXT, rental_rate NUMERIC, total_rental_duration INTERVAL );
– Create Summary Table CREATE TABLE summary_rental_report ( film_id INTEGER, title TEXT, total_rentals INTEGER, total_revenue NUMERIC );
D. SQL Query for Raw Data Extraction
SELECT r.rental_id, r.rental_date, r.return_date, r.customer_id, f.film_id, f.title, p.amount AS rental_rate, calculate_rental_duration(r.rental_date, r.return_date) AS total_rental_duration FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id JOIN payment p ON r.rental_id = p.rental_id; E. SQL Trigger to Update Summary Table
- Assistant
E. SQL Trigger to Update Summary Table
The goal of this part is to create a trigger that will automatically update the summary table whenever new data is added to the detailed table. Here is the SQL code to achieve this:
-- Create Function to Update Summary Table CREATE OR REPLACE FUNCTION update_summary_table() RETURNS TRIGGER AS $$ BEGIN -- If the film already exists in the summary table, update it IF EXISTS (SELECT 1 FROM summary_rental_report WHERE film_id = NEW.film_id) THEN UPDATE summary_rental_report SET total_rentals = total_rentals + 1, total_revenue = total_revenue + NEW.rental_rate WHERE film_id = NEW.film_id; ELSE -- If the film does not exist in the summary table, insert it INSERT INTO summary_rental_report (film_id, title, total_rentals, total_revenue) VALUES (NEW.film_id, NEW.title, 1, NEW.rental_rate); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Create Trigger on Detailed Table CREATE TRIGGER trg_update_summary AFTER INSERT ON detailed_rental_report FOR EACH ROW EXECUTE FUNCTION update_summary_table();This trigger ensures that every time a row is inserted into the
detailed_rental_reporttable, thesummary_rental_reporttable is updated to reflect the change.F. Stored Procedure to Refresh Data
We will now provide a stored procedure that can refresh the data in both the detailed and summary tables by clearing their contents and re-performing the raw data extraction.
-- Create Stored Procedure to Refresh Data CREATE OR REPLACE PROCEDURE refresh_rental_report() LANGUAGE plpgsql AS $$ BEGIN -- Clear the contents of the detailed and summary tables TRUNCATE TABLE detailed_rental_report; TRUNCATE TABLE summary_rental_report; -- Re-insert data into the detailed table INSERT INTO detailed_rental_report (rental_id, rental_date, return_date, customer_id, film_id, title, rental_rate, total_rental_duration) SELECT r.rental_id, r.rental_date, r.return_date, r.customer_id, f.film_id, f.title, p.amount AS rental_rate, calculate_rental_duration(r.rental_date, r.return_date) AS total_rental_duration FROM rental r JOIN inventory i ON r.inventory_id = i.inventory_id JOIN film f ON i.film_id = f.film_id JOIN payment p ON r.rental_id = p.rental_id; -- Re-populate summary table INSERT INTO summary_rental_report (film_id, title, total_rentals, total_revenue) SELECT film_id, title, COUNT(*) as total_rentals, SUM(rental_rate) as total_revenue FROM detailed_rental_report GROUP BY film_id, title; END; $$;F1. Job Scheduling Tool
A relevant job scheduling tool that can be used to automate the stored procedure is pg_cron, an extension for PostgreSQL that allows you to use cron syntax to schedule PostgreSQL commands. This can be set up to call the
refresh_rental_reportprocedure at regular intervals (e.g., weekly).-- Sample command to schedule weekly refresh using pg_cron SELECT cron.schedule('0 0 * * 0', 'CALL refresh_rental_report()');This schedule command will trigger the
refresh_rental_report()stored procedure every Sunday at midnight, ensuring the report is refreshed weekly and remains relevant to stakeholders.By following the structured process detailed above, you have the necessary SQL code to create a detailed rental report, automatically update the summary report, and refresh both tables at regular intervals. This will enhance business insights regarding rental patterns and top-performing films.