Join messages and users to find engagement score

Question

Write a query joining messages and messages to produce the combined engagement score.

Codemia · Accepted Answer

Break the problem into logical steps before writing SQL. Think about:

1. What tables do I need to join and on which keys?
2. What filtering (WHERE) do I need to apply?
3. Do I need aggregation (GROUP BY)?
4. Do I need window functions for ranking or running calculations?
5. Are there edge cases like NULLs, duplicates, or empty groups?

Use CTEs (WITH clauses) to break complex logic into readable, testable steps. Each CTE should handle one logical transformation.

```sql
WITH filtered_data AS (
  SELECT *
  FROM main_table
  WHERE condition = 'value'
    AND date_col >= '2024-01-01'
),
aggregated AS (
  SELECT
    group_col,
    COUNT(*) AS cnt,
    AVG(metric) AS avg_metric,
    ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY metric DESC) AS rn
  FROM filtered_data
  GROUP BY group_col
)
SELECT *
FROM aggregated
WHERE rn = 1
ORDER BY avg_metric DESC;
```

Adapt this pattern to the specific problem. The key is to filter first, then aggregate, then rank/select.

**Window Functions**: ROW_NUMBER(), RANK(), DENSE_RANK() for ranking. LAG()/LEAD() for accessing previous/next rows. SUM() OVER for running totals.

**JOIN Types**: INNER JOIN (matching rows only), LEFT JOIN (keep all left rows), FULL OUTER JOIN (keep all rows from both). Always think about what happens with NULLs after a LEFT JOIN.

**NULL Handling**: Use COALESCE(col, default) for null substitution. Remember: NULL = NULL is NULL (not TRUE). Use IS NULL / IS NOT NULL for null checks.

**Indexes**: Create indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Composite indexes should match your query's column order.

**Avoid**: SELECT * (only select needed columns), correlated subqueries (use JOINs instead), functions on indexed columns in WHERE (prevents index usage).

**Large Tables**: Use LIMIT for development, EXPLAIN ANALYZE to check query plans, and consider table partitioning for datasets over 100M rows.

Join messages and users to find engagement score

Compass

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Approach

Solution Pattern

Submit Your Answer

Compass Data Scientist Interview Guide

Related Questions

Optimize a slow query on orders

Write a query to find conversion rate from events

Write a query to find time to first action from sessions

Write a query to find DAU/MAU ratio from users

Calculate percentile rank per date