Skip to content
Project: Prepare for Data Analyst Associate Certification
  • AI Chat
  • Code
  • Report

    • Practical Exam Sample: Pet Supplies

    PetMind is a retailer of products for pets. They are based in the United States.

    PetMind sells products that are a mix of luxury items and everyday items. Luxury items include toys. Everyday items include food.

    The company wants to increase sales by selling more products for some animals repeatedly.

    They have been testing this approach for the last year.

    They now want a report on how repeat purchases impact sales.

    Data

    The data is available in the table pet_supplies.

    The dataset contains the sales records in the stores last year.

    Column NameCriteria
    product_idNominal. The unique identifier of the product.
    Missing values are not possible due to the database structure.
    categoryNominal. The category of the product, one of 6 values (Housing, Food, Toys, Equipment, Medicine, Accessory).
    Missing values should be replaced with “Unknown”.
    animalNominal. The type of animal the product is for. One of Dog, Cat, Fish, Bird.
    Missing values should be replaced with “Unknown”.
    sizeOrdinal. The size of animal the product is for. Small, Medium, Large.
    Missing values should be replaced with “Unknown”.
    priceContinuous. The price the product is sold at. Can be any positive value, round to 2 decimal places.
    Missing values should be replaced with the overall median price.
    salesContinuous. The value of all sales of the product in the last year. This can be any positive value, rounded to 2 decimal places.
    Missing values should be replaced with the overall median sales.
    ratingDiscrete. Customer rating of the product from 1 to 10.
    Missing values should be replaced with 0.
    repeat_purchaseNominal. Whether customers repeatedly buy the product (1) or not (0).
    Missing values should be removed.

    Task 1

    From taking a quick look at the data, you are pretty certain it isn't quite as it should be. You need to make sure all of the data is clean before you start your analysis. The table below shows what the data should look like.

    Write a query to return a table that matches the description provided.

    Do not update the original table.

    Column NameCriteria
    product_idNominal. The unique identifier of the product.
    Missing values are not possible due to the database structure.
    categoryNominal. The category of the product, one of 6 values (Housing, Food, Toys, Equipment, Medicine, Accessory).
    Missing values should be replaced with “Unknown”.
    animalNominal. The type of animal the product is for. One of Dog, Cat, Fish, Bird.
    Missing values should be replaced with “Unknown”.
    sizeOrdinal. The size of animal the product is for. Small, Medium, Large.
    Missing values should be replaced with “Unknown”.
    priceContinuous. The price the product is sold at. Can be any positive value, round to 2 decimal places.
    Missing values should be replaced with 0.
    salesContinuous. The value of all sales of the product in the last year. This can be any positive value, rounded to 2 decimal places.
    Missing values should be replaced with the overall median sales.
    ratingDiscrete. Customer rating of the product from 1 to 10.
    Missing values should be replaced with 0.
    repeat_purchaseNominal. Whether customers repeatedly buy the product (1) or not (0).
    Missing values should be removed.
    Unknown integration
    DataFrameavailable as
    clean_data
    variable
    SELECT product_id,
CASE WHEN category = '-' THEN 'Unknown' ELSE category END AS category,
animal,
INITCAP(size) AS size,
CASE WHEN price = 'unlisted' THEN 0 ELSE price::numeric END AS price,
sales,
CASE WHEN rating IS NULL THEN 0 ELSE rating END AS rating,
repeat_purchase
FROM pet_supplies;

    Task 2

    You want to show whether sales are higher for repeat purchases for different animals. You also want to give a range for the sales.

    Write a query to return the animal, repeat_purchase indicator and the avg_sales, along with the min_sales and max_sales. All values should be rounded to whole numbers.

    You should use the original pet_supplies data for this task.

    Unknown integration
    DataFrameavailable as
    animal_sales
    variable
    SELECT animal,
	repeat_purchase,
	ROUND(AVG(sales)) AS avg_sales,
	ROUND(MIN(sales)) AS min_sales,
	ROUND(MAX(sales)) AS max_sales
FROM pet_supplies
GROUP BY animal, repeat_purchase
ORDER BY repeat_purchase DESC, animal;

    Task 3

    The management team want to focus on efforts in the next year on the most popular pets - cats and dogs - for products that are bought repeatedly.

    Write a query to return the product_id, sales and rating for the relevant products.

    You should use the original pet_supplies data for this task.

    Unknown integration
    DataFrameavailable as
    popular_pet_products
    variable
    SELECT repeat_purchase, 
	product_id,
	sales,
	rating
FROM pet_supplies
WHERE animal IN ('Cat', 'Dog') AND repeat_purchase = 1
ORDER BY sales DESC;