Day 9: Cassandra Lab Practice – Advanced Queries & Aggregations

✅ Day 9: Advanced Queries & Aggregations

🎯 Objective:

Learn how to perform advanced queries, use aggregation functions, and understand ALLOW FILTERING in Cassandra.

---

📘 1. Aggregation Functions in Cassandra

Cassandra supports limited aggregation functions:

Function	Description
COUNT(*)	Count rows
MAX(column)	Maximum value in a column
MIN(column)	Minimum value in a column
AVG()	❌ Not supported natively
SUM()	❌ Not supported
WRITETIME()	Shows write timestamp of a column
TTL()	Shows remaining TTL of a column

---

🧪 2. Example: Count Records

SELECT COUNT(*) FROM test_lab.employee;

---

📊 3. Example: MIN / MAX

SELECT MIN(joined_on), MAX(joined_on) FROM test_lab.employee;

---

⏰ 4. WRITETIME and TTL

SELECT WRITETIME(department), TTL(department)
FROM test_lab.employee
WHERE emp_id = <UUID>;

---

⚠️ 5. Using ALLOW FILTERING

Cassandra requires partition key for most queries. If not used, you must add ALLOW FILTERING.

➤ Example:

SELECT * FROM test_lab.employee
WHERE department = 'Finance' ALLOW FILTERING;

⚠️ Use ALLOW FILTERING only for testing or small datasets. It can hurt performance.

---

🔍 6. Pagination with LIMIT and OFFSET (Simulated)

Cassandra doesn’t support OFFSET, but you can simulate paging:

SELECT * FROM test_lab.employee LIMIT 5;

• For real pagination, use token(partition_key) with a cursor-like method in application logic.

---

🧪 Day 9 Lab Tasks

1. Use COUNT, MIN, MAX on employee and exam_scores tables
2. Check WRITETIME and TTL on any column
3. Run a query using ALLOW FILTERING on a non-key column
4. Try paginating with LIMIT

---

✅ Checklist

Task	Done
Used COUNT, MIN, MAX functions	✅
Fetched WRITETIME and TTL for a column	✅
Executed query with ALLOW FILTERING	✅
Tested result limiting with LIMIT	✅