Meta-style Company ChallengeBeginnerVerified answerSQLite live

Active Users List

Which users currently have an active account?

  • Date analysis
  • Filtering
  • Sorting

Challenge brief

Understand the request

Trust & Safety needs a current roster of all accounts in good standing for a compliance audit.

List all active users with username, email, full name, country, and registration date.

Return

  • username
  • email
  • full_name (first + last)
  • country
  • registration_date

Constraints

  • Return active accounts only
  • Show the newest registrations first

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

users

  • user_idINTEGER
  • usernameVARCHAR(50)
  • emailVARCHAR(100)
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • registration_dateDATETIME
  • countryVARCHAR(50)
  • statusVARCHAR(20)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

All data is in a single table: users. The status column holds the account state. Filter to 'active' — no JOIN required.

Hint 2

WHERE status = 'active'. Concatenate first_name and last_name with || ' ' || to build full_name. ORDER BY registration_date DESC.

Hint 3

Start with the tables that establish the result grain for question 1, select the required output aliases, and add the remaining joins, filters, aggregation, and ordering one clause at a time.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT username, email, first_name || ' ' || last_name AS full_name, country, registration_date FROM users WHERE status = 'active' ORDER BY registration_date DESC;

Why this works

status = 'active' excludes alex_brown (inactive). The || operator concatenates strings in SQLite: first_name || ' ' || last_name produces 'Sarah Jones'.

Success check

4 active users — Sarah, Mike, Jane, John (alex_brown is inactive)

Expected result

Use this output to verify values, aliases, ordering, and row count.

usernameemailfull_namecountryregistration_date
sarah_jonessarah@email.comSarah JonesCanada2020-04-05 14:20:00
mike_wilsonmike@email.comMike WilsonUK2020-03-20 09:15:00
jane_smithjane@email.comJane SmithUSA2020-02-15 11:30:00
john_doejohn@email.comJohn DoeUSA2020-01-10 10:00:00

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.