Premium

#193 SQL Queries for Profiles, Followers, and Articles

Companies:
Aston Rosbank
Themes:
SQLJoinsAggregationPractice

The following tables are given:

CREATE TABLE profile
(
    id BIGSERIAL PRIMARY KEY,
    nickname VARCHAR,
    registered_at TIMESTAMP
);

CREATE TABLE subscription_count
(
    profile_id BIGINT REFERENCES profile (id) UNIQUE,
    followers_count INT, -- number of followers
    following_count INT  -- number of subscriptions
);

CREATE TABLE article
(
    id BIGSERIAL PRIMARY KEY,
    owner_id BIGINT REFERENCES profile (id),
    article TEXT, -- article
    created_in TIMESTAMP,
    likes_count INT -- number of likes
);

It is necessary to:

  • Select the nickname values of profiles that have more than 1000 followers.
  • Select the id values of profiles that have more than 10 articles.

Question review

Mentor
Answer as you would in a real interview. I’ll ask follow-up questions, then compare your answer with the reference answer and show your accuracy as a %.
Sign in to start the dialogue The mentor keeps the context of the current question and your previous messages.
Sign in