Emoji Usage
ASKED IN INTERVIEWSlack
Problem Statement
Slack admins want to identify high-energy channels. One metric they use is the frequency of the :fire: emoji within a channel's history.
Write an SQL query to count how many times the emoji :fire: was used in channel_id 101.
Rules:
- Consider only channel_id 101.
- Match the emoji string :fire: exactly.
- Output columns: channel_id, fire_emoji_count.
- Return the result in descending order of the count.
Table Schema:
- ChannelMessages: message_id, channel_id, user_id, emoji_used (VARCHAR), sent_at.
Tests your understanding of
Basic SQL, Aggregation, Filtering and String Matching
Input Tables
ChannelMessages
| channel_id(INTEGER) | emoji_used(VARCHAR) |
|---|---|
| 101 | :fire: |
| 101 | :smile: |
| 102 | :fire: |
| 101 | :fire: |
| 101 | :check: |
| 101 | :fire: |
| 103 | :fire: |
Expected Output
| channel_id(INTEGER) | fire_emoji_count(INTEGER) |
|---|---|
| 101 | 3 |
Tags
EasyASKED IN INTERVIEWBasic SQLAggregationFilteringString Matching
5-10 min
88%