WGU D326 Task 1 Data Analysis |Latest Update with Complete Solution
Hayato Ikeno
Advanced Data Management - D326
April 09, 2024
Task 1 Data Analysis
A)
The DVD Rental Service has been a great success. We plan to share this success with
our most loyal customers; At the end of every month, the marketing team will be
providing free rental vouchers to ten customers that have rented the highest number of
DVDs.
Source Table Column Name Data Types & Destination Definition
Precision
customer customer_id INT Detailed & The ids of the
Summary customers
used as the
primary key.
, customer customer_name VARCHAR(91) Detailed & First name and
Summary last name from
the customer
table is
extracted and
combined as a
single column
using a
function.
city city VARCHAR(50) Detailed City name from
the city table is
extracted
country country VARCHAR(50) Detailed Country name
from the
country table is
extracted
rental rental_count INT Detailed & Total number
Summary of rentals is
calculated
using the count
of DVD rentals
a customer has
placed in the
most recent
month based
off of the
number of
rental_ids.
The..summary..report..and..detailed..report..includes..the..customer..name..and..number..of
..rentals..that..can..be..used..to..identify..the..customers..with..the..most..number..of..rentals;..
Additionally,..the..detailed..report..also..provides..further..information..of..the..customer,..suc
h..as..city..and..country..they..reside.
The..vouchers..given..are..provided..on..a..monthly..basis..and..thus..the..report..should..likewi
se..be..updated..at..the..end..of..each..month.
B)
CREATE..OR..REPLACE..FUNCTION..get_customer_name(IN..customer_id_param..INT)..
RETURNS..VARCHAR(91)..AS
$$..DECLA
RE
Hayato Ikeno
Advanced Data Management - D326
April 09, 2024
Task 1 Data Analysis
A)
The DVD Rental Service has been a great success. We plan to share this success with
our most loyal customers; At the end of every month, the marketing team will be
providing free rental vouchers to ten customers that have rented the highest number of
DVDs.
Source Table Column Name Data Types & Destination Definition
Precision
customer customer_id INT Detailed & The ids of the
Summary customers
used as the
primary key.
, customer customer_name VARCHAR(91) Detailed & First name and
Summary last name from
the customer
table is
extracted and
combined as a
single column
using a
function.
city city VARCHAR(50) Detailed City name from
the city table is
extracted
country country VARCHAR(50) Detailed Country name
from the
country table is
extracted
rental rental_count INT Detailed & Total number
Summary of rentals is
calculated
using the count
of DVD rentals
a customer has
placed in the
most recent
month based
off of the
number of
rental_ids.
The..summary..report..and..detailed..report..includes..the..customer..name..and..number..of
..rentals..that..can..be..used..to..identify..the..customers..with..the..most..number..of..rentals;..
Additionally,..the..detailed..report..also..provides..further..information..of..the..customer,..suc
h..as..city..and..country..they..reside.
The..vouchers..given..are..provided..on..a..monthly..basis..and..thus..the..report..should..likewi
se..be..updated..at..the..end..of..each..month.
B)
CREATE..OR..REPLACE..FUNCTION..get_customer_name(IN..customer_id_param..INT)..
RETURNS..VARCHAR(91)..AS
$$..DECLA
RE
