Domain 1EasyCORE

Ads Performance

The problem requires analyzing the performance of ads based on given data. The inputs include two tables: `Ads` and `Performance`. The `Ads` table contains information about each ad, including its ID, name, and status. The `Performance` table records the performance metrics for each ad, such as impressions, clicks, and cost.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

1322. Ads Performance

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 58.3%


Problem Breakdown

The problem requires analyzing the performance of ads based on given data. The inputs include two tables: Ads and Performance. The Ads table contains information about each ad, including its ID, name, and status. The Performance table records the performance metrics for each ad, such as impressions, clicks, and cost.

Inputs:

  • Ads: A table with columns ad_id, name, and status.
  • Performance: A table with columns ad_id, impressions, clicks, and cost.

Outputs:

  • A list of ads sorted by their performance metrics, typically click-through rate (CTR) or cost per click (CPC).

Implicit Constraints/Edge Cases:

  • The Ads table may contain duplicate ad IDs.
  • The Performance table may have missing entries for some ads.
  • The status column in the Ads table might include values like ‘active’, ‘inactive’, etc.

Intuition

To solve this problem, we need to join the two tables on ad_id, calculate relevant performance metrics (like CTR or CPC), and then sort the results. A brute-force approach would involve iterating through each ad, calculating metrics, and sorting them manually, which is inefficient in terms of time complexity.

The key insight is to leverage SQL’s built-in functions for joining tables and performing calculations, which allows us to achieve this efficiently without manual iteration. By using a single query with appropriate joins and aggregations, we can directly obtain the desired results.

Approach

  1. Join Tables: Perform an inner join between the Ads and Performance tables on ad_id to combine relevant data.
  2. Calculate Metrics: Compute performance metrics such as CTR (clicks/impressions) or CPC (cost/clicks).
  3. Filter Data: Optionally, filter ads based on their status (e.g., only active ads).
  4. Sort Results: Order the results by the calculated metric in descending order to get the best-performing ads first.
  5. Select Columns: Choose the necessary columns for the output.

Complexity Analysis

  • Time Complexity: O(n log n), where n is the number of rows in the Performance table, due to the sorting operation.
  • Space Complexity: O(n), as we need to store the joined data and intermediate results.

Solution

import pandas as pd

def ads_performance(ads: pd.DataFrame, performance: pd.DataFrame) -> pd.DataFrame:
    # Join Ads and Performance tables on ad_id
    merged_df = ads.merge(performance, on='ad_id', how='inner')
    
    # Calculate CTR (Click-Through Rate)
    merged_df['CTR'] = merged_df['clicks'] / merged_df['impressions']
    
    # Filter active ads (optional based on requirement)
    filtered_df = merged_df[merged_df['status'] == 'active']
    
    # Sort by CTR in descending order
    sorted_df = filtered_df.sort_values(by='CTR', ascending=False)
    
    # Select necessary columns for output
    result_df = sorted_df[['ad_id', 'name', 'CTR']]
    
    return result_df

Edge Cases Handled

  • Missing Performance Data: The inner join ensures that only ads with performance data are considered.
  • Duplicate Ad IDs: The join operation naturally handles duplicates by combining all matching rows.
  • Inactive Ads: The filtering step allows excluding inactive ads from the results if needed.