1 Giới thiệu

Đề thi này được soạn dựa trên các đề thi tuyển Data Analyst, BI Analyst và Data Engineer thực tế và được thiết kế để kiểm tra khả năng sử dụng SQL để trích xuất, xử lý và phân tích dữ liệu của ứng viên cho các vị trí ở level junior.

Dataset được sử dụng là dataset trong bộ database “Sakila” và các câu hỏi sẽ xoay quanh dataset này.

Sakila là một database chứa các bảng dữ liệu thể hiện thông tin về 1 cửa hàng cho thuê DVD, bao gồm những nội dung như phim, diễn viên, mối quan hệ giữa diễn viên với phim, …

2 Yêu cầu

Sử dụng bộ Sakila database bằng 1 trong các cách sau:

Download và cài đặt mysql (google hướng dẫn trên mạng, nên cài đặt thêm MySQL Workbench để viết lệnh và sử dụng MySQL). Bạn vẫn hoàn toàn có thể sử dụng 1 hệ quản trị cơ sở dữ liệu khác (VD: SQL Server, PostgreSQL, …) tuy nhiên có thể sẽ có 1 vài điểm khác biệt nho nhỏ (nhưng không ảnh hưởng trọng yếu tới kiến thức và thực hành của bài học này). Sau đó tải cài đặt bộ Sakila database theo link sau: https://dev.mysql.com/doc/sakila/en/sakila-installation.html
Sử dụng Google Colab notebook sau đây để truy vấn dữ liệu của bộ Sakila database mà không cần cài đặt gì trên máy tính cá nhân: https://colab.research.google.com/drive/1koov_VNKwvrY_3ZbZ_5S3bU3ZVldaabj?usp=sharing

Entity Relationship (ER) diagram: https://github.com/tuanphan92/datatute-datasets/blob/main/JobSeeker/sakila%20dataset/sakila-db/sakila_model.png

Hãy phân tích và trả lời các câu hỏi sau đây. Câu trả lời cần bao gồm SQL query và kết quả trả ra khi chạy query đó.

3 Về đáp án

Các SQL query trong đáp án được viết để sử dụng trong Google BigQuery. Với các SQL dialects khác như MySQL, PostgreSQL, SQL Server, … vẫn hoàn toàn áp dụng được bình thường, nhưng đôi chỗ sẽ cần điều chỉnh lại (ví dụ lệnh LIMIT trong SQL Server sẽ phải sửa thành SELECT TOP)

Với các câu hỏi mang tính chất mở như câu 4 và câu 5 thì không có 1 đáp án nào duy nhất chính xác cả, đây là những câu hỏi để kiểm tra sự nhạy bén với dữ liệu, sự sáng tạo, sự cẩn thận, khả năng trình bày, …bên cạnh việc kiểm tra mức độ thành thạo SQL của ứng viên.

4 Câu hỏi

4.1 Câu hỏi 1:

Foreign key và Primary key khác nhau như thế nào? Từ bộ dữ liệu được cung cấp, hãy nêu ra ví dụ về 3 foreign keys

Đáp án:

Nêu ra định nghĩa về FK và PK như đã học ở Lesson 1.

Các khác biệt chính giữa FK và PK như sau:

No	Primary key	Foreign key
1	được sử dụng để đảm bảo giá trị trong mỗi cột (hoặc bộ các cột) là duy nhất	được sử dụng để tạo ra liên kết giữa 2 bảng với nhau
2	1 bảng chỉ có 1 khóa chính (PK)	1 bảng có thể có nhiều hơn 1 khóa ngoại (FK)
3	PK phải bao gồm các giá trị duy nhất, không được chứa giá trị NULL	FK có thể chứa các giá trị trùng lặp và giá trị NULL

Ví dụ về 3 FOREIGN KEYS trong bộ dữ liệu:

film.language_id là FOREIGN KEY của bảng film
city.country_id là FOREIGN KEY của bảng city
payment.rental_id là FOREIGN KEY của bảng payment

4.2 Câu hỏi 2:

Viết lệnh để trả ra danh sách các diễn viên bao gồm mã ID, tên, họ, và sắp xếp theo số lượng phim họ đã đóng, theo từng loại ngôn ngữ.

Đáp án:
Query:

--lưu ý: để query này trả ra kết quả chính xác, nhớ phải kiểm tra để 
--đảm bảo `actor_id` là khóa chính của bảng `actor`
SELECT
  a.actor_id
  , a.first_name
  , a.last_name
  , d.name AS language
  , COUNT(DISTINCT c.film_id) AS no_films_acted
FROM  
  actor a
LEFT JOIN 
  film_actor b ON a.actor_id = b.actor_id
LEFT JOIN 
  film c ON b.film_id = c.film_id
LEFT JOIN 
  language d ON c.language_id = d.language_id
GROUP BY
  a.actor_id
  , a.first_name
  , a.last_name
  , c.language_id
  , d.name
ORDER BY c.language_id, no_films_acted DESC

Kết quả:

q2 <- read.csv2("data/q2.csv", header = TRUE, sep = ",", quote = "\"")
datatable(q2)

4.3 Câu hỏi 3:

Sử dụng 2 bảng dữ liệu city và country, cho biết quốc gia nào có nhiều thành phố nhất?

Kết quả trả ra bao gồm:

Tên quốc gia
Số lượng thành phố

Đáp án:

WITH tbl1 AS (
SELECT 
    country_id
  , COUNT(DISTINCT city_id) AS no_cities
FROM
    city
GROUP BY
    country_id
)
SELECT
  country.country
  , tbl1.no_cities
FROM
    tbl1
LEFT JOIN
    country ON tbl1.country_id = country.country_id
WHERE 
    tbl1.no_cities = (SELECT MAX(no_cities) FROM tbl1)

Kết quả:

q3 <- read.csv2("data/q3.csv", header = TRUE, sep = ",", quote = "\"")
q3 %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

country	no_cities
India	60

4.4 Câu hỏi 4:

Hãy trình bày những điều khác mà bạn quan sát, nhận thấy được từ bộ data này, cung cấp SQL query và kết quả của query.

Đáp án tham khảo:

4.4.1 Customers’ emails:

Email của khách hàng trong bộ data này được tạo nên bởi công thức: first_name + “.” + last_name + “@sakilacustomer.org”

SQL query để confirm nhận định trên:

WITH x AS (
SELECT
    first_name
    , last_name
    , email
    , CONCAT(first_name, '.', last_name, '@sakilacustomer.org') AS email2
FROM customer
)
SELECT * 
FROM x
WHERE email <> email2 -- 0 rows, which means the formula for email is correct

4.4.2 Customers’ active rate:

~97.5% khách hàng là active

SQL query:

WITH tbl1 AS(
SELECT
  active,
  COUNT(DISTINCT customer_id) as no_customers,
FROM customer
GROUP BY active
)
SELECT
  active,
  no_customers,
  ROUND(CAST(no_customers AS float64) / (SELECT SUM(no_customers) FROM tbl1) * 100, 1) AS percentage
FROM tbl1

Kết quả:

active_rate <- read.csv2("data/active_rate.csv", header = TRUE, sep = ",", quote = "\"")
active_rate %>% kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")

active	no_customers	percentage
1	584	97.5
0	15	2.5

4.4.3 Top 5 quốc gia có nhiều khách hàng nhất:

SQL query:

WITH tbl1 as (
SELECT
  a.customer_id, 
    b.city_id,
    c.city,
    d.country_id,
    d.country
FROM 
    customer a
LEFT JOIN
    address b ON a.address_id = b.address_id
LEFT JOIN
    city c ON b.city_id = c.city_id
LEFT JONI
    country d ON c.country_id = d.country_id
)
SELECT
  country, 
    COUNT(DISTINCT customer_id) AS no_customers,
    (SELECT COUNT(DISTINCT customer_id) from tbl1) as total_customers,
  round(100*count(1) / (select count(1) from tbl1), 2) as pct
FROM tbl1
GROUP BY country
ORDER BY pct desc
LIMIT 5;

Kết quả:

df <- read.csv2("data/customers_by_country_top_5.csv", header = TRUE, sep = ",", quote = "\"")
  
df <- 
  df %>% 
  mutate(pct = pct %>% as.character %>% as.numeric)
  

# df %>% str  
order <- df %>% 
  arrange(pct %>% desc) %>% 
  head(5) %>% 
  pull(country) %>% as.vector


df %>% 
  arrange(pct %>% desc) %>% 
  head(5) %>% 
  mutate(country = fct_relevel(country,order)) %>% 
  ggplot() +
  aes(x=country, y=no_customers) +
  geom_col(position = "dodge", fill = "#1e81b0") +
  labs(title = "Top 5 countries with the highest # customers",
       x = "",
       y = ""
  ) +
  geom_text(aes(label=paste0(format(no_customers, big.mark = ",", scientific = FALSE), "\n", pct, "%")), vjust=-0.5)+
  #scale_fill_brewer(palette = "Set1") +
  theme(legend.position = "none",
        # axis.text.x = element_blank(), 
        axis.text =  element_text(size = 11),
        axis.ticks.x = element_blank(),
        axis.text.y = element_blank(),
        axis.ticks.y = element_blank(),
        panel.grid = element_blank(), 
        panel.background = element_rect(fill = "white"),
        plot.caption = element_text(face = "italic"), 
        plot.subtitle = element_text(face = "italic")
  )+
  scale_y_continuous(expand = c(0,0,0.3,0))

4.4.4 Payment:

Vì ngày cho thuê (rental_date) luôn bằng ngày thanh toán tiền (payment_date) nên ta có thể giả định rằng khách hàng phải trả tiền ngay mỗi khi thuê 1 mặt hàng nào đó. Khoản thanh toán này được ghi nhận vào bảng payment. Nếu khách hàng không phải thanh toán (được mượn miễn phí) thì số tiền thanh toán ghi nhận vào bảng payment sẽ bằng 0.

SQL query:

SELECT
  count(1)
FROM 
    rental a
LEFT JOIN
    payment b ON a.rental_id = b.rental_id
WHERE rental_date = payment_date -- all 16044 rows --> rental_date always = payment_date

Có 183 trường hợp khách hàng chưa trả lại hàng đã thuê. Trong đó, 182 trường hợp xảy ra vào ngày 12 tháng 2, 2006. Ta có thể giải thích lý do chưa trả 182 trường hợp này là vì đây là những trường hợp thuê mới, do theo bộ dữ liệu này thì dữ liệu của cửa hàng được cập nhật lần cuối vào 23 tháng 2, 2006.

SQL query:

SELECT
    rental_date
    , last_update
    , count(1) AS count_rows
FROM rental
WHERE return_date is NULL
GROUP BY rental_date, last_update

new_rent <- read.csv2("data/new_rent.csv", header = TRUE, sep = ",", quote = "\"")
formattable(new_rent)

rental_date	last_update	count_rows
2006-02-14 15:16:03.000000 UTC	2006-02-15 21:30:53.000000 UTC	182
2005-08-21 00:30:32.000000 UTC	2006-02-23 04:12:08.000000 UTC	1

Có 1 khách hàng thuê đồ vào năm 2005 nhưng có vẻ đã quên không trả lại. Thông tin chi tiết về khách hàng đó như sau:

SQL query:

SELECT
    a.rental_id
    , a.inventory_id
    -- , c.film_id
    , c.title AS film_title
    -- , a.customer_id
    , d.first_name AS customer_first_name
    , d.last_name AS customer_last_name
    , d.email AS customer_email
    , a.rental_date
    , a.return_date
    --, a.staff_id
    , e.email AS staff_email    
FROM 
  rental a 
LEFT JOIN
  inventory b ON a.inventory_id = b.inventory_id
LEFT JOIN 
  film c ON b.film_id = c.film_id
LEFT JOIN 
  customer d ON a.customer_id = d.customer_id
LEFT JOIN 
  staff e ON a.staff_id = e.staff_id
WHERE a.rental_date = '2005-08-21 00:30:32'

cust_2005 <- read.csv2("data/2005.csv", header = TRUE, sep = ",", quote = "\"")
datatable(cust_2005, options = list(
        scrollX = TRUE,
        scrollY = FALSE)
)

Trong số 183 trường hợp thuê đồ nêu trên, 24 trường hợp có payment amount = 0 (mượn đồ miễn phí), 159 trường hợp có payment amount > 0 (thuê đồ có trả phí)

SQL query:

SELECT
  a.rental_id,
  a.rental_date,
  a.return_date,
  b.payment_id,
  b.amount,
  b.payment_date,
  b.last_update
FROM 
    rental a
LEFT JOIN
    payment b ON a.rental_id = b.rental_id
WHERE 
  a.return_date IS NULL

rent_183 <- read.csv2("data/183.csv", header = TRUE, sep = ",", quote = "\"")
datatable(rent_183, options = list(
        scrollX = FALSE,
        scrollY = FALSE)
)

4.4.5 Rental amount:

Số tiền thuê mà khách hàng phải trả có vẻ như phụ thuộc vào bộ phim và thời gian thuê

SQL query:

WITH x AS ( -- calculate rental duration
SELECT
  a.rental_id
  , c.film_id
  , DATE_DIFF(CAST(return_date AS date), CAST(rental_date AS date), day) AS rental_duration
  , b.amount
FROM 
  rental a
LEFT JOIN 
  payment b ON a.rental_id = b.rental_id
LEFT JOIN 
  inventory c ON a.inventory_id = c.inventory_id
WHERE 1=1
  AND b.amount IS NOT NULL
  AND b.amount <> 0
)
SELECT
  film_id
  , rental_duration
  , count(distinct amount) -- count number of unique amount by film_id, rental_duration
FROM x
WHERE rental_duration IS NOT NULL
GROUP BY
  film_id
  , rental_duration
HAVING COUNT(DISTINCT amount) > 1 -- This confirm that film_id and rental_duration uniquely identify the rental amount

4.4.6 Rental date

Dữ liệu thuê đồ của cửa hàng có từ 24 tháng 5, 2005 đến 23 tháng 8, 2005, và có xuất hiện 1 trường hợp ngoại lệ nằm ngoài khoảng thời gian này, đó là ngày 14 tháng 2, 2006

SQL query:

WITH tbl1 AS(
SELECT
    CAST(rental_date AS date) AS rental_date
FROM rental
)
SELECT
  rental_date,
  COUNT(1) as no_rows
FROM tbl1
GROUP BY rental_date
ORDER BY rental_date DESC;

rental_dates <- read.csv2("data/rental_dates.csv", header = TRUE, sep = ",", quote = "\"")
datatable(rental_dates, options = list(
        scrollX = FALSE,
        scrollY = FALSE)
)

4.4.7 Tỷ lệ thuê trung bình theo từng nhóm hàng

Games, Travel, và Sci-Fi là top 3 nhóm có tỷ lệ thuê trung bình cao nhất.

SQL query:

SELECT
    c.name AS category
    , ROUND(AVG(a.rental_rate), 2) AS avg_rental_rate
FROM 
    film a
LEFT JOIN
    film_category b ON a.film_id = b.film_id
LEFT JOIN
    category c ON b.category_id = c.category_id
GROUP BY
    c.name
ORDER BY avg_rental_rate DESC

rental_rate_by_categ <- read.csv2("data/rental_rate_by_categ.csv", header = TRUE, sep = ",", quote = "\"")
datatable(rental_rate_by_categ, options = list(
        scrollX = FALSE,
        scrollY = FALSE)
)

4.4.8 Những bộ phim không có hàng để thuê

Có 42 phim không có hàng để cho thuê (không có bản ghi nào trong bảng inventory)

SQL query:

SELECT
  a.film_id,
  a.title,
  a.description,
  a.release_year,
  a.language_id,
  a.rating
FROM 
    film a
WHERE NOT EXISTS (SELECT 1 FROM inventory b WHERE a.film_id = b.film_id)

film_no_inventory <- read.csv2("data/film_no_inventory.csv", header = TRUE, sep = ",", quote = "\"")
datatable(film_no_inventory, options = list(
        scrollX = FALSE,
        scrollY = FALSE)
)

4.4.9 Doanh thu

Ta có thể tính doanh thu cho từng cửa hàng và tổng doanh thu của toàn bộ chuỗi bằng cách cộng tổng các khoản thanh toán lại.

SQL query:

WITH tbl1 AS (
SELECT
    a.rental_id,
    b.payment_id,
    b.amount,
    c.store_id
FROM 
    rental a
LEFT JOIN
    payment b ON a.rental_id = b.rental_id
LEFT JOIN
    inventory c ON a.inventory_id = c.inventory_id
)
SELECT
    store_id,
    sum(amount) AS revenue,
    (SELECT SUM(amount) FROM tbl1) AS total_revenue,
    ROUND(100*SUM(amount) / (SELECT SUM(amount) FROM tbl1), 2) AS pct
FROM tbl1
GROUP BY store_id

revenue_by_store <- read.csv2("data/revenue_by_store.csv", header = TRUE, sep = ",", quote = "\"")

# formattable(revenue_by_store)

df <- revenue_by_store

# df %>% 
#   select(total_revenue) %>% distinct %>% 
#   # kbl() %>% kable_styling(bootstrap_options = "striped", full_width = F, position = "left")
#   knitr::kable(caption="Total revenue")

df %>% 
  mutate(store_id = case_when(
    store_id == 1 ~ "Store 1",
    TRUE ~ "Store 2"
  )) %>% 
  mutate(revenue = revenue %>% as.character %>% as.numeric %>% round(.,0),
         pct = pct %>% as.character %>% as.numeric) %>% 
  ggplot() +
  aes(x=store_id, y=revenue) +
  geom_col(position = "dodge", fill = "#1e81b0") +
  labs(title = "Revenue by store",
       subtitle = "Total revenue = ~67,407",
       x = "",
       y = ""
  ) +
  geom_text(aes(label=paste0(format(revenue, big.mark = ",", scientific = FALSE), "\n", pct, "%")), vjust=-0.5)+
  #scale_fill_brewer(palette = "Set1") +
  theme(legend.position = "none",
        # axis.text.x = element_blank(), 
        axis.text =  element_text(size = 11),
        axis.ticks.x = element_blank(),
        axis.text.y = element_blank(),
        axis.ticks.y = element_blank(),
        panel.grid = element_blank(), 
        panel.background = element_rect(fill = "white"),
        plot.caption = element_text(face = "italic"), 
        plot.subtitle = element_text(face = "italic")
  )+
  scale_y_continuous(expand = c(0,0,0.3,0))

4.5 Câu hỏi 5:

Hãy trình bày những việc bạn sẽ làm để làm sạch bộ dữ liệu này. Đưa ra SQL query và kết quả để thể hiện ra những trường hợp cần làm sạch (không cần viết query làm sạch dữ liệu, tuy nhiên bạn vẫn có thể làm nếu muốn)

4.5.1 Thừa dữ liệu tiêu đề phim và mô tả phim

Cả bảng film_text và bảng film đều có dữ liệu về tiêu đề phim và mô tả phim. Tuy các tiêu đề và mô tả này giống hệt nhau trong phạm vi của bộ dữ liệu này, việc thừa dữ liệu này có thể phát sinh những vấn đề sau trong tương lai:

Thiếu hiệu quả về mặt dung lượng lưu trữ dữ liệu
Rủi ro dữ liệu không đồng nhất giữa 2 bảng, đặc biệt có thể xảy ra trong quá trình insert thêm dữ liệu hoặc update dữ liệu cũ

Vì vậy tôi đề xuất thiết kế lại các bảng này, giữ lại bảng film và xóa bảng film_text

SQL query để confirm tiêu đề và mô tả các phim trong 2 bảng hiện tại là như nhau:

WITH x AS (
SELECT
    a.film_id,
    a.title,
    b.title as title_film_text,
    a.description,
    b.description AS description_film_text
FROM
  film a
LEFT JOIN
  film_text b ON a.film_id = b.film_id
)
SELECT
  *
FROM x
WHERE 
  title <> title_film_text
  OR description <> description_film_text
  
-- The result returns 0 rows, which means titles and descriptions from the 2 tables are the same

4.5.2 Các cột với tất cả bản ghi đều NULL hoặc có giá trị trốn

Các cột sau có 100% các bản ghi nhận 1 trong 2 giá trị: NULL hoặc blank. Vì vậy, trong bối cảnh thực tế, ta sẽ cần phải xem lại quy trình mà các cột này được tạo ra để tìm ra nguyên nhân gốc rễ của vấn đề thiếu dữ liệu này.

Trong bối cảnh bài test này, tôi sẽ chỉ note lại vấn đề và không sử dụng các cột này.

Các cột có 100% giá trị NULL hoặc blank:

address.address2
film.original_language_id

SQL query:

-- address.address2
SELECT
    COUNT(1) AS no_rows
FROM address
WHERE address2 IS NULL OR address2 = ''
-- all 603 rows are either NULL or blank

-- film.original_language_id
SELECT
    COUNT(1) AS no_rows
FROM film
WHERE original_language_id IS NULL OR original_language_id = 0
-- all 1000 rows are either NULL or blank

4.5.3 Phim thiếu thông tin diễn viên

Có 3 bộ phim thiếu thông tin về diễn viên

SQL query:

SELECT
    a.film_id,
    a.title,
    b.actor_id
FROM
  film a
LEFT JOIN 
  film_actor b ON a.film_id = b.film_id
WHERE b.film_id IS NULL

films_missing_actor_info <- read.csv2("data/films_missing_actor_info.csv", header = TRUE, sep = ",", quote = "\"")
formattable(films_missing_actor_info)

film_id	title	actor_id
257	DRUMLINE CYCLONE	NA
323	FLIGHT LIES	NA
803	SLACKER LIAISONS	NA

Trong thực tế, khi thực hiện phân tích dữ liệu để tìm kiếm nguyên nhân, giải pháp cho 1 vấn đề nào đó, tôi sẽ bỏ qua không phân tích các bộ phim này. Còn khi làm báo cáo BI mà yêu cầu cung cấp thông tin chi tiết cho 100% tất cả các bộ phim thì tôi sẽ giữ các bộ phim này lại và thông báo cho người dùng báo cáo về các thông tin bị thiếu. Song song đó, tôi sẽ làm việc với các bên liên quan để xử lý vấn đề về dữ liệu này.

4.5.4 Thiếu thông tin về thuê phim

Có 5 khoản thanh toán bị thiếu thông tin về việc thuê phim

SQL query:

SELECT
  *
FROM payment a
WHERE NOT EXISTS (
  SELECT 1 
  FROM rental b 
  WHERE a.rental_id = b.rental_id 
  )

null_rental_id <- read.csv2("data/null_rental_id.csv", header = TRUE, sep = ",", quote = "\"")
formattable(null_rental_id)

payment_id	customer_id	staff_id	amount	payment_date	last_update
424	16	1	1.99	2005-06-18 04:56:12.000000 UTC	2006-02-15 22:12:32.000000 UTC
14675	546	1	3.99	2005-07-30 21:16:20.000000 UTC	2006-02-15 22:21:38.000000 UTC
10840	401	1	0.99	2005-07-12 06:26:10.000000 UTC	2006-02-15 22:17:49.000000 UTC
7011	259	2	1.99	2005-08-23 06:13:16.000000 UTC	2006-02-15 22:14:58.000000 UTC
15458	577	2	0.99	2005-05-27 00:46:39.000000 UTC	2006-02-15 22:22:32.000000 UTC

Trong thực tế, khi thực hiện phân tích dữ liệu để tìm kiếm nguyên nhân, giải pháp cho 1 vấn đề nào đó, tôi sẽ bỏ qua không phân tích các khoản thanh toán này. Còn khi làm báo cáo BI mà yêu cầu cung cấp thông tin chi tiết cho 100% tất cả các khoản thanh toán thì tôi sẽ giữ các khoản này lại và thông báo cho người dùng báo cáo về các thông tin bị thiếu. Song song đó, tôi sẽ làm việc với các bên liên quan để xử lý vấn đề về dữ liệu này.

4.5.5 Thiếu thông tin về district, postal code và số điện thoại

Các địa chỉ sau đây thiếu dữ liệu về district, postal code và số điện thoại

SQL query:

SELECT
    address_id,
    address,
    district,
    postal_code,
    phone
FROM address
WHERE
    (district IS NULL OR district = '') -- 3 blank rows
  OR (postal_code IS NULL) -- 4 NULL rows
  OR (phone IS NULL) -- 2 NULL rows

address_table_missing_values <- read.csv2("data/address_table_missing_values.csv", header = TRUE, sep = ",", quote = "\"", colClasses = c("character", "character", "character", "character", "character"))

formattable(address_table_missing_values)

address_id	address	district	postal_code	phone
4	1411 Lillydale Drive	QLD		6172235589
2	28 MySQL Boulevard	QLD
386	1368 Maracabo Boulevard		32716	934352415130
519	962 Tama Loop		65952	282667506728
3	23 Workhaven Lane	Alberta		14033335568
1	47 MySakila Drive	Alberta
30	18 Duisburg Boulevard		58327	998009777982

Làm giàu dữ liệu:

Với các trường hợp thiếu postal codes, ta có thể lấy postal codes từ internet dựa trên địa chỉ (với giả định là địa chỉ chính xác). Tuy nhiên các địa chỉ trong bộ dữ liệu này lại không phải là địa chỉ thật(VD: 28 MySQL Boulevard). Do vậy, tôi không thực hiện hoạt động làm giàu dữ liệu nào với trường này.
Với các trường hợp thiếu dữ liệu districts, ta cũng có thể lấy tên quận dựa trên địa chỉ. Tuy nhiên với bộ dữ liệu này, các địa chỉ có vẻ không hoàn toàn khớp với các thành phố (VD: với address_id = 30, ta có địa chỉ là 18 Duisburg Boulevard, là 1 nơi tại nước Đức, trong khi trường thông tin city thì lại ghi là Citt del Vaticano). Vì vậy, tôi không thực hiện hoạt động làm giàu dữ liệu nào với trường này.
Với các trường hợp thiếu số điện thoại, trong bối cảnh bài test này, tôi không thực hiện hoạt động làm giàu dữ liệu nào do không có cách nào để tìm ra các số điện thoại bị thiếu

Job Seeker - Lesson 3
Đáp án

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com

06/2023

1 Giới thiệu

2 Yêu cầu

3 Về đáp án

4 Câu hỏi

4.1 Câu hỏi 1:

4.2 Câu hỏi 2:

4.3 Câu hỏi 3:

4.4 Câu hỏi 4:

4.4.1 Customers’ emails:

4.4.2 Customers’ active rate:

4.4.3 Top 5 quốc gia có nhiều khách hàng nhất:

4.4.4 Payment:

4.4.5 Rental amount:

4.4.6 Rental date

4.4.7 Tỷ lệ thuê trung bình theo từng nhóm hàng

4.4.8 Những bộ phim không có hàng để thuê

4.4.9 Doanh thu

4.5 Câu hỏi 5:

4.5.1 Thừa dữ liệu tiêu đề phim và mô tả phim

4.5.2 Các cột với tất cả bản ghi đều NULL hoặc có giá trị trốn

4.5.3 Phim thiếu thông tin diễn viên

4.5.4 Thiếu thông tin về thuê phim

4.5.5 Thiếu thông tin về district, postal code và số điện thoại

Job Seeker - Lesson 3 Đáp án

Tuan Phan https://datatute.vn tuanphan.datatute@gmail.com

06/2023

1 Giới thiệu

2 Yêu cầu

3 Về đáp án

4 Câu hỏi

4.1 Câu hỏi 1:

4.2 Câu hỏi 2:

4.3 Câu hỏi 3:

4.4 Câu hỏi 4:

4.4.1 Customers’ emails:

4.4.2 Customers’ active rate:

4.4.3 Top 5 quốc gia có nhiều khách hàng nhất:

4.4.4 Payment:

4.4.5 Rental amount:

4.4.6 Rental date

4.4.7 Tỷ lệ thuê trung bình theo từng nhóm hàng

4.4.8 Những bộ phim không có hàng để thuê

4.4.9 Doanh thu

4.5 Câu hỏi 5:

4.5.1 Thừa dữ liệu tiêu đề phim và mô tả phim

4.5.2 Các cột với tất cả bản ghi đều NULL hoặc có giá trị trốn

4.5.3 Phim thiếu thông tin diễn viên

4.5.4 Thiếu thông tin về thuê phim

4.5.5 Thiếu thông tin về district, postal code và số điện thoại

Job Seeker - Lesson 3
Đáp án

Tuan Phan
https://datatute.vn
tuanphan.datatute@gmail.com