BC2402 Designing & Developing Databases Individual Assignment Semester 1, 2025/26 | NTU

Latte Logic: Uncovering Coffee Trends with SQL

1. Introduction

A. The Preamble

Coffee isn’t just a beverage; it’s an industry pulsing with regional trends, exponential growth, and shifting business models. To set the stage for your SQL assignment, consider the following landscape across Southeast Asia and North America:

Southeast Asia: Rapid Expansion & Local Champions

• Café Amazon sold an astonishing 400 million cups in 2024, about 1.1 million per day, and now operates nearly 4,922 outlets globally, with 4,485 in Thailand alone.

Source: The Nation

• Malaysian-born Zuspresso (ZUS Coffee) is set to open up to 200 new stores this year, surpassing Starbucks in Malaysia.

Source: Bloomberg

• Indonesian chains are scaling fast: Kopi Kenangan now boasts over 900 outlets across the region, while Fore Coffee has launched 210+ stores in Indonesia, with expansion into Singapore.
• These growth trajectories underscore the importance of tracking key metrics, including outlet counts, daily cup sales, geographic expansion, and local versus global brand comparisons.

North America: Maturing Market & Strategic Pivoting

• The U.S. café industry is projected to generate around $74 billion in 2025, marked by a mature yet fragmented sector where chains and independents alike compete fiercely.
• Big names continue to evolve: Starbucks is renovating 1,000 stores to emphasise them as community “third places” and mandating more in-office days for corporate staff to reinvigorate its café roots.
• Meanwhile, Royal Coffee is raising retail prices due to rising bean costs, tariffs, and elevated logistics—a change likely to ripple across the sector.
• These trends underscore various data questions, which we explore in this assignment.

2. Data Descriptions

You are provided with a dataset extracted from various open sources. Refer to the corresponding sources for column descriptions and background information. The dataset consists of multiple tables. Specifically,

1. baristacoffeesalestbl
2. caffeine_intake_tracker
3. coffeesales

3 added columns:
coffeeID
shopID
customer_id

See Q8 for details.

4. consumer preference
5. list_coffee_shops_in_kota_bogor
6. top-rated-coffee

To mimic standard security policies in work settings, you must use the SQL database implementations provided with this document to complete your assignment. Likewise, you SHOULD NOT modify the database (i.e., data types, table structures) to complete this assignment.

3. Project Deliverables

The due date for the assignment is 3 October 2025 (23:59 hrs NTULearn server time)

You are only expected to submit one file, as follows:
1 x SQL script file

You are tasked to develop some SQL scripts to query the data, as follows:

You are to create SQL scripts (e.g., SELECT statements) that generate answers for the following queries.

IMPORTANT: You are encouraged to provide in-line comments to explain your logic. Critically, if you encounter data issues (e.g., data types) that necessitate cleansing and/or conversion, you must explain the reasons and your corresponding strategies. If your query output differs from the suggested output, you must justify your output if you believe your output is accurate or more fitting to the question. Otherwise, your solution will be deemed invalid.

1. [1 mark] Table considered: <baristacoffeesalesTBL>
How many product categories are there? For each product category, show the number of records.

2. [2 marks] Table considered: <baristacoffeesalesTBL>
For each customer_gender and loyalty_member type, show the number of records. Within the same outcome, within each customer_gender and loyalty_member type, for each is_repeat_customer type, show the number of records.

3. [1 mark] Table considered: <baristacoffeesalesTBL>
For each product_category and customer_discovery_source, display the sum of total_amount.

A / B versions required with inline comments to justify differences.

4. [1 mark] Table considered: <caffeine_intake_tracker>
For each time_of_day category and gender, display the average focus_level and average sleep_quality.

5. [1 mark] Table considered: <coffeesales>
List the amount of spending (money) recorded before 12 and after 12.
IMPORTANT: Discuss any data issues observed with inline comments.

6. [1 mark] Tables considered: <consumerpreference>

Consider 7 categories of Ph values

pH >= 0.0 && pH < 1.0
pH >= 1.0 && pH < 2.0
pH >= 2.0 && pH < 3.0
pH >= 3.0 && pH < 4.0
pH >= 4.0 && pH < 5.0
pH >= 5.0 && pH < 6.0
pH >= 6.0 && pH < 7.0

For each category of pH values, show the average Liking, FlavorIntensity, Acidity, and Mouthfeel.

7. [3 mark] Tables considered: <coffeesales> + <list_coffee_shops_in_kota_bogor> + <top-rated-coffee> + <baristacoffeesalestbl>

IMPORTANT: The table name “top-rated-coffee” contains hyphens.

Background: The barista group (baristacoffeesalestbl) and coffee shops in kota bogor (list_coffee_shops_in_kota_bogor) have a common loyalty program. This question aims to reveal the synergy of this program (e.g., identifying the profitable combinations of outlets)

The 4 tables can be joint using the following structure and logic:

Consider the above table structures (i.e., columns):
coffeesales.coffeeID = `top-rated-coffee`.ID

coffeesales.shopID = list_coffee_shops_in_kota_bogor.no

coffeesales.customer_id = baristacoffeesalestbl.customer_id*

* The matching between the 2 customer_id is not straightforward

coffeesales.customer_id = 1 while baristacoffeesalestbl.customer_id = CUST_1

After joining the 4 tables, for each trans_month (coffeesaeles.date), list the top 3 combinations of store_id (baristacoffeesalestbl) and shopID (coffeesales) based on the sum of money (coffeesales).

The expected output is as follows:

[The above is an illustration of the expected result structures (i.e., columns). The specific values can be inaccurate (e.g., data type conversions).]

4. Submission

A submission folder will be made available on NTULearn. You can make as many submissions as necessary, but only the latest submission will be evaluated.

The submission must be made by 3 October 2025, 23:59.