# 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;
```
Sign in with Wallet
Connect another wallet