# 사용자 페이지 지표

**URL:** <https://meta.discourse.org/t/user-page-metrics/354906>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [10월 31, 2024, 12:52오전 UTC](https://meta.discourse.org/t/user-page-metrics/354906 "2024-10-31T00:52:40Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [11월 1, 2024, 8:23오후 UTC](https://meta.discourse.org/t/user-page-metrics/354906/4 "2024-11-01T20:23:26Z")

</div>

> [@srinivas.chilukuri](#):
>
> 시작 날짜와 종료 날짜가 필요합니다. 시작 날짜와 종료 날짜를 지정하여 해당 메트릭을 가져오는 우회 방법이 있을까요?
> 
> - 사용자
> - 받은 좋아요
> - 준 좋아요
> - 본 토픽
> - 토픽 수
> - 게시글 수
> - 방문일 수
> - 읽은 게시글
> - 해결책
> - 치어(응원)

사이트의 사용자에 대한 해당 메트릭을 특정 시작 날짜와 종료 날짜로 필터링하여 확인하려면, 각 메트릭에 대한 데이터를 별도의 CTE(Common Table Expression)로 가져오고, 최종 `SELECT` 문에서 결과를 결합하는 쿼리가 필요합니다.

다음과 같이 작성할 수 있습니다:

**사용자 메트릭**

```sql
-- [params]
-- date :start_date = 2020-01-01
-- date :end_date = 2025-01-01

WITH likes_received AS (
    SELECT 
        ua.user_id AS user_id,
        COUNT(*) AS likes_received
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 2
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.user_id
),
likes_given AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS likes_given
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 1
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
user_metrics AS (
    SELECT 
        us.user_id,
        SUM(us.topics_entered) AS topics_viewed,
        SUM(us.posts_read_count) AS posts_read,
        SUM(us.days_visited) AS days_visited
    FROM 
        user_stats us
    WHERE 
        us.first_post_created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        us.user_id
),
solutions AS (
    SELECT 
        ua.acting_user_id AS user_id,
        COUNT(*) AS solutions
    FROM 
        user_actions ua
    WHERE 
        ua.action_type = 15 
        AND ua.created_at BETWEEN :start_date AND :end_date
    GROUP BY 
        ua.acting_user_id
),
cheers AS (
    SELECT 
        gs.user_id,
        SUM(gs.score) AS cheers
    FROM 
        gamification_scores gs
    WHERE 
        gs.date BETWEEN :start_date AND :end_date
    GROUP BY 
        gs.user_id
)

SELECT 
    u.id AS user_id,
    COALESCE(lr.likes_received, 0) AS likes_received,
    COALESCE(lg.likes_given, 0) AS likes_given,
    COALESCE(um.topics_viewed, 0) AS topics_viewed,
    COALESCE(um.posts_read, 0) AS posts_read,
    COALESCE(um.days_visited, 0) AS days_visited,
    COALESCE(sol.solutions, 0) AS solutions,
    COALESCE(ch.cheers, 0) AS cheers
FROM 
    users u
LEFT JOIN 
    likes_received lr ON u.id = lr.user_id
LEFT JOIN 
    likes_given lg ON u.id = lg.user_id
LEFT JOIN 
    user_metrics um ON u.id = um.user_id
LEFT JOIN 
    solutions sol ON u.id = sol.user_id
LEFT JOIN 
    cheers ch ON u.id = ch.user_id
ORDER BY 
    u.id

```

**쿼리 설명:**

1. **매개변수** :

- `:start_date`와 `:end_date`는 조회할 데이터의 날짜 범위를 정의하는 매개변수입니다.

1. **공용 테이블 표현식 (CTE)**:

- **likes\_received** : 지정된 날짜 범위 내에서 각 사용자가 받은 좋아요 수(`action_type = 2`)를 계산합니다.
- **likes\_given** : 지정된 날짜 범위 내에서 각 사용자가 준 좋아요 수(`action_type = 1`)를 계산합니다.
- **user\_metrics** : 지정된 날짜 범위 내에서 첫 게시글을 작성한 사용자의 본 토픽 수, 읽은 게시글 수, 방문일 수 등 사용자 통계를 집계합니다.
- **solutions** : 지정된 날짜 범위 내에서 각 사용자가 제공한 해결책 수(`action_type = 15`)를 계산합니다.
- **cheers** : 지정된 날짜 범위 내에서 각 사용자의 게이미피케이션 점수를 합산합니다.

1. **최종 선택** :

- 메인 쿼리는 받은 좋아요, 준 좋아요, 본 토픽, 읽은 게시글, 방문일 수, 제공한 해결책, 받은 치어 등 각 사용자의 참여 메트릭을 선택합니다.
- 일부 카테고리에서 활동이 없는 사용자도 포함되도록 `LEFT JOIN`을 사용하며, `COALESCE`를 사용하여 0으로 채웁니다.

**결과 예시**

| user\_id | likes\_received | likes\_given | topics\_viewed | posts\_read | days\_visited | solutions | cheers |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1 | 10 | 5 | 20 | 100 | 15 | 2 | 30 |
| 2 | 0 | 3 | 5 | 20 | 5 | 0 | 10 |
| — | — | — | — | — | — | — | — |

---

_[View the full topic](https://meta.discourse.org/t/user-page-metrics/354906)._