---
# System prepended metadata

title: CreatorDB Backend Engineer Interview Test

---

# CreatorDB Backend Engineer Interview Test

- [Question](https://hackmd.io/@l8mSeLGERMCGy_KadfR22A/S1N45Q9Zee)
- [Reference answer](https://hackmd.io/@l8mSeLGERMCGy_KadfR22A/BJJfN8yGex)

---

# 1. Algorithm - Fill in Missing Daily Metrics

---
You are working on a social media analytics platform. The system provides an API that returns a creator’s recent metric data. The API returns at most 7 metric entries and at least 1 metric entry, sorted in ascending order by date. However, the entries might not cover 7 consecutive days due to missing data in the database.

Your task is to implement a function:
`fillMissingMetrics(data: Metric[])`: Metric[] that fills in the missing days so that the result contains exactly 7 days of data, from 6 days ago up to today.

---

## Input

- data: an array of up to 7 Metric objects, sorted by date (ascending):
```typescript=
type Metric = {
  date: number; // UTC timestamp at midnight
  averageLikesCount: number;
  followersCount: number;
  averageEngagementRate: number;
};

```
- The function should generate entries for the date range:

```
[today - 6d, ..., today]
```

- where "today" is the current date at midnight UTC.

```
(i.e., new Date().setUTCHours(0, 0, 0, 0))

```

---

## Fill-in Logic
---

For each missing day:
- Look for the nearest available date (this can be from either the past or future).
- If two dates are equally distant, prefer the older date (i.e., the earlier date).
- Fill the missing days by using the nearest date according to the rules above.
- Return a list of exactly 7 metrics, one for each day in the target date range, sorted in ascending order.

---
## Example Input
---
```typescript=
const example1 = [
  {
    date: 1738368000000,           // -11d
    averageLikesCount: 100,
    followersCount: 200,
    averageEngagementRate: 0.01
  },
  {
    date: 1738540800000,           // -9d
    averageLikesCount: 105,
    followersCount: 202,
    averageEngagementRate: 0.012
  },
  {
    date: 1738713600000,           // -7d
    averageLikesCount: 110,
    followersCount: 205,
    averageEngagementRate: 0.015
  },
  {
    date: 1738800000000,           // -6d
    averageLikesCount: 120,
    followersCount: 208,
    averageEngagementRate: 0.02
  },
  {
    date: 1739068800000,           // -3d
    averageLikesCount: 130,
    followersCount: 210,
    averageEngagementRate: 0.022
  },
  {
    date: 1739155200000,           // -2d
    averageLikesCount: 140,
    followersCount: 215,
    averageEngagementRate: 0.023
  },
  {
    date: 1739328000000,           // 0d
    averageLikesCount: 150,
    followersCount: 220,
    averageEngagementRate: 0.025
  },
];

// Today is represented by 1739328000000 in this example     

```
```typescript=
const example2 = [
  {
    date: 1738886400000,           // -5d
    averageLikesCount: 120,
    followersCount: 208,
    averageEngagementRate: 0.02
  },
  {
    date: 1739328000000,           // 0d
    averageLikesCount: 150,
    followersCount: 220,
    averageEngagementRate: 0.025
  },
];

// Today is represented by 1739328000000 in this example     


```

---
## 🎯 Expected Output
---
- Example Output from example1:

```typescript=
[
  { date: ..., data from -6d },  // -6d → original
  { date: ..., data from -6d },  // -5d → fallback to -6d
  { date: ..., data from -3d },  // -4d → fallback to -3d
  { date: ..., data from -3d },  // -3d → original
  { date: ..., data from -2d },  // -2d → original
  { date: ..., data from -2d },  // -1d → fallback to -2d
  { date: ..., data from  0d }   //  0d → original
]


```
- Example Output from example2:

```typescript=
[
  { date: ..., data from -5d }, // -6d → fallback forward to -5d
  { date: ..., data from -5d }, // -5d → original
  { date: ..., data from -5d }, // -4d → fallback to -5d
  { date: ..., data from -5d }, // -3d → fallback to -5d
  { date: ..., data from -5d }, // -2d → fallback to -5d (equal distance to 0d, prefer older)
  { date: ..., data from  0d }, // -1d → fallback to 0d
  { date: ..., data from  0d }, //  0d → original
]

```


---
## 📝 Function Skeleton

```typescript=
/**

Fills in missing metrics to ensure complete data for the last 7 days
@param data - Array of metric objects sorted by date in ascending order
@returns Array of metrics with exactly 7 days of data (from 6 days ago to today)
*/
function fillMissingMetrics(data: Metric[]): Metric[] {
// Your implementation here

}

```

---


## Solution

- 撇開資料結構，就是找最小值。
- 時間複雜度，`Q(7*n)`
- 
```cpp=
struct Metric {
    long long date; // UTC timestamp at midnight
    int averageLikesCount;
    int followersCount;
    double averageEngagementRate;
};


long long getTodayMidnightUTC() {
    time_t now = time(nullptr);
    tm* utc = gmtime(&now);
    utc->tm_hour = 0;
    utc->tm_min = 0;
    utc->tm_sec = 0;
    return static_cast<long long>(mktime(utc)) * 1000;
}

Metric findClosedDay(long long date, Metric[] & data) {
    // Your implementation here
    Metric closest = data[0];
    long long minDistance = abs(data[0].date - targetDate);
    for (const auto& metric : data) {
        long long distance = abs(metric.date - targetDate);
         if (distance < minDistance  || 
              (distance == minDistance && metric.date < closest.date)
            ) {
            closest = metric;
            minDistance = distance;
         }
        
    }
    
    Metric result = closest;
    result.date = targetDate;
    return result;
    
    
    
}
vector<Metric> fillMissingMetrics(const vector<Metric>& data) {
    const long long MS_PER_DAY = 86400000LL;
    long long today = getTodayMidnightUTC();
    
        
    vector<Metric> ret;
    // Your implementation here
    for (int i = 6; i >= 0; --i) {
        long long date = today - i * MS_PER_DAY;
        
        Metric day = findClosedDay(date, data);
        
        ret.push_back(day);
    }
    return ret;
}



```


---

# 2. Debug
---
Help me to debug this code, and explan why

```typescript=
async function getPage(url) {
    var response = await fetch(url);
    var data = await response.text();
    return data;
    }

async function getYoutubeData(youtubeIds) {
    var promises = [];
    for (var i = 0; i < youtubeIds.length; i++) {
        var promise = new Promise(async (resolve, reject) => {
            try {
                var channelURL = https://www.youtube.com/${youtubeIds[i]};
                var channelPage = await getPage(channelURL);

                var videosURL = `https://www.youtube.com/${youtubeIds[i]}/videos`;
                var videosPage = await getPage(videosURL);

                resolve({ channelPage, videosPage });
              } catch (e) {
                reject(e);
              }
            });
        promises.push(promise);
    }
    
    var results = await Promise.all(promises);
    return results;
    }


var youtubeIds = ['@darbbq', '@oojimateru', '@homemeat_clip'];
getYoutubeData(youtubeIds);
```


---
## Solution
---
- Bug
    - 字串插值語法錯誤 
    ```typescript=
    const channelURL = `https://www.youtube.com/${youtubeId}`;
    ```
    - Promise 使用不當：在循環中不必要地建立新 Promise
    - 串行請求效率低：依序請求每個頻道，沒有充分利用並行處理
    - 一個頻道失敗會導致整個請求失敗
- 改進說明



```typescript=
async function getPage(url) {
    const response = await fetch(url);
    const data = await response.text();
    return data;
}

async function getChannelData(youtubeId) {
    try {
        const channelURL = `https://www.youtube.com/${youtubeId}`;
        const videosURL = `https://www.youtube.com/${youtubeId}/videos`;
        
        const [channelPage, videosPage] = await Promise.all([
            getPage(channelURL),
            getPage(videosURL)
        ]);
        
        return { channelPage, videosPage };
    } catch (e) {
        console.error(`获取 ${youtubeId} 数据失败:`, e);
        return null; // 或者可以根据需要处理错误
    }
}

async function getYoutubeData(youtubeIds: string[]): Promise<ChannelData[]> {
    const promises = [];
    
    // 使用 for 循环创建 promise 数组
    for (let i = 0; i < youtubeIds.length; i++) {
        promises.push(getChannelData(youtubeIds[i]));
    }
    
    // 等待所有 promise 完成
    const allResults = await Promise.all(promises);
    
    // 过滤 null 值
    const finalResults = [];
    for (const result of allResults) {
        if (result) {
            finalResults.push(result);
        }
    }
    
    return finalResults;
}

const youtubeIds = ['@darbbq', '@oojimateru', '@homemeat_clip'];
getYoutubeData(youtubeIds).then(console.log).catch(console.error);
```

---
# 3. Database Schema Design
---

- Scenario:
You need to design a database schema for a headless API service with **quota counting**. The system will track API usage, manage user credits, and provide usage analytics.

- Requirements
    - Credit Management: Track prepurchased credits and deduct them based on API endpoint usage
    - Usage Tracking: Record detailed API call information for auditing and troubleshooting
    - Historical Record Keeping: Store monthly usage data for analytics and reporting
    - Monthly Analysis: Enable users to view their API usage aggregated by month
    - Endpoint-Specific Quotas: Different API endpoints have different credit costs
```typescript=
const enum APIEndpoint {
  // creator
  SubmitCreators = '/submit-creators',
  DiscoverCreators = '/discover-creators',
  GetCreatorInfo = '/get-creator-info',
  
  // keyword
  GetTopicItems = '/get-topic-items',
  GetNicheItems = '/get-niche-items',
  GetHashtagItems = '/get-hashtag-items',
  
  // Add other endpoints as needed
}

const apiQuotaMap: Record<APIEndpoint, number> = {
  // creator
  [APIEndpoint.SubmitCreators]: 1,
  [APIEndpoint.DiscoverCreators]: 2, 
  [APIEndpoint.GetCreatorInfo]: 3,
  
  // keyword
  [APIEndpoint.GetTopicItems]: 1,
  [APIEndpoint.GetNicheItems]: 1,
  [APIEndpoint.GetHashtagItems]: 1,
  
  // Add other mappings as needed
} as const

```
---
### Environment Constraints:
---

- Database: Choose either NoSQL (MongoDB/Firestore) OR SQL (MySQL/PostgreSQL) based on your assessment
- Integration: Service is already in use and will be extended with this new feature (maintain backward compatibility)
- Performance: Design must be scalable and extendable to handle growing usage
- Efficiency: Schema should minimize processing time and operational costs
- Documentation: Schema should be readable and easily explainable to non-technical stakeholders

---
### Task:
---
Complete the `UserTableScheme` and any other necessary types by designing appropriate database schema structures to fulfill the requirements above. You need to implement the `apiUsageHistory` field and any additional tables or structures needed.

```typescript=
// design database storage scheme
type UserTableScheme =
{
    userId: string;
    prepurchasedCredit: number;

    // implement here
    apiUsageHistory: ???
    ...
}
```


---
### Solution:
---

```typescript=
// API端点类型（保持不变）
const enum APIEndpoint {
  // creator
  SubmitCreators = '/submit-creators',
  DiscoverCreators = '/discover-creators', 
  GetCreatorInfo = '/get-creator-info',
  
  // keyword
  GetTopicItems = '/get-topic-items',
  GetNicheItems = '/get-niche-items',
  GetHashtagItems = '/get-hashtag-items',
}

// 配额映射（保持不变）
const apiQuotaMap: Record<APIEndpoint, number> = {
  [APIEndpoint.SubmitCreators]: 1,
  [APIEndpoint.DiscoverCreators]: 2,
  [APIEndpoint.GetCreatorInfo]: 3,
  [APIEndpoint.GetTopicItems]: 1,
  [APIEndpoint.GetNicheItems]: 1,
  [APIEndpoint.GetHashtagItems]: 1,
} as const;

// 主用户表
type UserTableScheme = {
  userId: string;                  // 用户唯一ID
  email: string;                   // 用户邮箱
  prepurchasedCredit: number;      // 预购信用点数
  currentMonthCreditUsed: number;  // 当月已用信用
  createdAt: Date;                 // 账户创建时间
  updatedAt: Date;                 // 最后更新时间
};

// API使用记录表
type ApiUsageRecord = {
  recordId: string;        // 记录唯一ID
  userId: string;          // 关联用户ID
  endpoint: APIEndpoint;   // 调用的API端点 
  creditsUsed: number;     // 消耗的信用点数
  timestamp: Date;         // 调用时间
  status: 'success' | 'failed'; // 调用状态
  responseSize?: number;    // 响应数据大小（可选）
  ipAddress?: string;      // 调用IP（可选）
};

// 月度汇总表
type MonthlyUsageSummary = {
  summaryId: string;       // 汇总ID
  userId: string;          // 关联用户ID
  year: number;            // 年份
  month: number;           // 月份 (1-12)
  totalCreditsUsed: number; // 该月总使用量
  endpointBreakdown: {      // 按端点分类的使用量
    endpoint: APIEndpoint;
    count: number;
    creditsUsed: number;
  }[];
};

// 信用变更记录表（可选）
type CreditTransaction = {
  transactionId: string;   // 交易ID
  userId: string;          // 关联用户ID
  amount: number;          // 变更数量（正数为增加）
  type: 'purchase' | 'usage' | 'manual_adjustment' | 'refund';
  referenceId?: string;     // 关联ID（如订单ID）
  timestamp: Date;         // 变更时间
  remainingCredit: number; // 变更后剩余信用
};
```






1. 信用扣除流程

Credit Management: Track prepurchased credits and deduct them based on API endpoint usage


```typescript=
async function deductCredits(userId: string, endpoint: APIEndpoint) {
  // 开始事务
  const creditsNeeded = apiQuotaMap[endpoint];
  
  // 1. 检查用户信用
  const user = await db.user.findByPk(userId);
  if (user.prepurchasedCredit < creditsNeeded) {
    throw new Error('信用不足');
  }
  
  // 2. 扣除信用（原子操作）
  await db.user.update({
    prepurchasedCredit: user.prepurchasedCredit - creditsNeeded,
    currentMonthCreditUsed: user.currentMonthCreditUsed + creditsNeeded
  }, { where: { userId } });
  
  // 3. 记录使用日志
  await db.usageRecords.create({
    userId,
    endpoint,
    creditsUsed: creditsNeeded,
    timestamp: new Date(),
    status: 'success'
  });
  
  // 4. 更新月度汇总（异步）
  updateMonthlySummary(userId, endpoint, creditsNeeded);
}
```

```sql=
BEGIN;

-- 1. 扣除信用（含檢查條件）
UPDATE "user"
SET 
  prepurchased_credit = prepurchased_credit - :creditsNeeded,
  current_month_credit_used = current_month_credit_used + :creditsNeeded
WHERE user_id = :userId
  AND prepurchased_credit >= :creditsNeeded;

-- 若沒有成功扣除（ROW_COUNT = 0），應在應用層 ROLLBACK 並報錯

-- 2. 新增使用紀錄
INSERT INTO api_usage_record (
  record_id,
  user_id,
  endpoint,
  credits_used,
  timestamp,
  status
) VALUES (
  gen_random_uuid(),     -- 或使用 uuid_generate_v4()，需安裝 pgcrypto 或 uuid-ossp
  :userId,
  :endpoint,
  :creditsNeeded,
  :timestamp,
  'success'
);

-- 3. 更新或插入月度彙總（upsert）
INSERT INTO monthly_usage_summary (
  user_id,
  year,
  month,
  endpoint,
  count,
  credits_used
) VALUES (
  :userId,
  EXTRACT(YEAR FROM :timestamp)::int,
  EXTRACT(MONTH FROM :timestamp)::int,
  :endpoint,
  1,
  :creditsNeeded
)
ON CONFLICT (user_id, year, month, endpoint)
DO UPDATE SET
  count = monthly_usage_summary.count + 1,
  credits_used = monthly_usage_summary.credits_used + EXCLUDED.credits_used;

COMMIT;

```



2. 每月報表生成

Monthly Analysis: Enable users to view their API usage aggregated by month

```typescript=
async function generateMonthlyReport(userId: string, year: number, month: number) {
  // 从预计算的月度汇总表获取
  const report = await db.monthlySummaries.findOne({
    where: { userId, year, month }
  });
  
  // 或实时聚合（性能较低但更准确）
  const realtimeReport = await db.usageRecords.findAll({
    where: { 
      userId,
      timestamp: {
        [Op.between]: [
          new Date(year, month - 1, 1), 
          new Date(year, month, 0)
        ]
      }
    },
    group: ['endpoint'],
    attributes: [
      'endpoint',
      [db.fn('COUNT', 'id'), 'count'],
      [db.fn('SUM', 'creditsUsed'), 'creditsUsed']
    ]
  });
  
  return report || realtimeReport;
}
```
- 針對某api 用量 給出 月報
```sql=
SELECT 
  endpoint,
  COUNT(record_id) AS count,
  SUM(credits_used) AS creditsUsed
FROM 
  usage_records
WHERE 
  user_id = '使用者ID' AND
  timestamp >= '2025-05-01' AND 
  timestamp < '2025-06-01'
GROUP BY 
  endpoint;


```