Streamhub's engagement analyst is reviewing interaction events.
Write a query to return the ID and event type of every event whose event type contains click or view, regardless of capitalization.
Assumptions:
- The
eventstable has one row per event with anidand anevent_type. - A qualifying event has an
event_typecontaining the substringclickor the substringviewsomewhere in the string, with case ignored.
Output:
- One row per qualifying event, with columns
idandevent_type.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
event_type
FROM
events
WHERE
event_type ~* 'click|view'The shape
~* is the case-insensitive regex operator, and the pattern click|view is two substrings joined by alternation. A single predicate covers page_view and upgrade_clicked and would also catch any PAGE_VIEW or Upgrade_Clicked variant, because the case fold happens automatically on both sides of the match.
Clause by clause
SELECT id, event_typereturns the event ID and its type for the engagement review.FROM eventsreads the events table.WHERE event_type ~* 'click|view'keeps rows whose event type containsclickorview. The*suffix on~flips the operator to case-insensitive matching. The|separates the two alternatives. The regex is not anchored, so the substring can appear at any position in the event type.
Why this and not ILIKE '%click%' OR ILIKE '%view%'
Both forms return the same rows. The regex collapses two ILIKE predicates joined by OR into one predicate with two alternatives. The savings grow with the number of alternatives. The trade-off is readability: an analyst who hasn't worked with POSIX regex will read ILIKE faster. On a team that already uses regex, the ~* spelling is fine.
The trap
The four POSIX operators are easy to confuse. ~ is case-sensitive match; ~* is case-insensitive match; !~ is case-sensitive negation; !~* is case-insensitive negation. The * always means "case fold the comparison." The ! always means "negate the result." Mixing them up changes which rows the predicate keeps, and the wrong operator returns rows that look superficially correct, which makes the mistake easy to ship.
You practiced ~* — case-insensitive regex matching with alternation, the right operator when capitalization should be ignored on both sides of the comparison.
