Table: twitter_user_mention - Query Twitter User Mentions using SQL

Twitter User Mentions are instances when a Twitter user includes another user's Twitter handle in their tweet. This is often done to draw the mentioned user's attention to the tweet or to engage them in a conversation. User mentions are a key part of the interactive nature of Twitter, allowing users to engage with each other and fostering discussions on the platform.

Table Usage Guide

The twitter_user_mention table provides insights into Twitter User Mentions, specifically the details of the users who have mentioned the authenticated user in their tweets. As a social media analyst, explore user-specific details through this table, including the frequency of mentions, the context of mentions, and associated metadata. Utilize it to uncover information about user interactions, such as the most active users, the nature of interactions, and the reach of your tweets.

Important Notes

The user_id field must be set in the where clause.

Examples

Last 5 tweets that mention the user

Explore the most recent social media mentions of a specific user to stay updated with the latest conversations involving them. This can be particularly useful for monitoring brand reputation, tracking user engagement, or responding to customer feedback.

select
  id,
  text
from
  twitter_user_mention
where
  user_id = '1318177503995985921' -- @steampipeio
order by
  created_at desc
limit
  5;

select
  id,
  text
from
  twitter_user_mention
where
  user_id = '1318177503995985921' -- @steampipeio
order by
  created_at desc
limit
  5;

Get mention timeline by username

Discover the specific instances where a particular username is mentioned on Twitter. This can be useful in monitoring brand mentions, tracking conversations involving a specific user, or understanding user engagement trends. Via subselect:

select
  id,
  text
from
  twitter_user_mention as t
where
  t.user_id in (
    select
      id
    from
      twitter_user
    where
      username = 'steampipeio'
  );

select
  id,
  text
from
  twitter_user_mention as t
where
  t.user_id in (
    select
      id
    from
      twitter_user
    where
      username = 'steampipeio'
  );

Via join:

select
  t.id,
  t.text
from
  twitter_user_mention as t,
  twitter_user as u
where
  t.user_id = u.id
  and u.username = 'steampipeio';

select
  t.id,
  t.text
from
  twitter_user_mention as t,
  twitter_user as u
where
  t.user_id = u.id
  and u.username = 'steampipeio';

Schema for twitter_user_mention

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
attachment_polls	jsonb		Polls attached to the Tweet.
attachments	jsonb		Specifies the type of attachments (if any) present in this Tweet.
author	jsonb		Author of the Tweet.
author_id	text		Unique identifier of the author of the Tweet.
cashtags	jsonb		List of cashtags (e.g. $TWTR) mentioned in the Tweet.
context_annotations	jsonb		Contains context annotations for the Tweet.
conversation_id	text		The Tweet ID of the original Tweet of the conversation (which includes direct replies, replies of replies).
created_at	timestamp with time zone		Creation time of the Tweet.
entities	jsonb		Contains details about text that has a special meaning in a Tweet.
geo	jsonb		Contains details about the location tagged by the user in this Tweet, if they specified one.
hashtags	jsonb		List of hashtags (e.g. #sql) mentioned in the Tweet.
id	text		Unique identifier of this Tweet.
in_reply_to_user_id	text		If this Tweet is a Reply, indicates the user ID of the parent Tweet's author.
in_reply_user	jsonb		User the Tweet was in reply to.
lang	text		Language of the Tweet, if detected by Twitter. Returned as a BCP47 language tag.
mentions	jsonb		List of users (e.g. steampipeio) mentioned in the Tweet.
mentions_obj	jsonb		Users mentioned in the Tweet.
place	jsonb		Place where the Tweet was created.
possibly_sensitive	boolean		Indicates if this Tweet contains URLs marked as sensitive, for example content suitable for mature audiences.
public_metrics	jsonb		Engagement metrics for the Tweet at the time of the request.
quoted	text		If this Tweet is a Quote Tweet, indicates the ID of the original Tweet.
referenced_tweets	jsonb		Tweets referenced in this Tweet.
replied_to	text		If this Tweet is a Reply, indicates the ID of the Tweet it is a reply to.
retweeted	text		If this Tweet is a Retweet, indicates the ID of the orginal Tweet.
source	text		The name of the app the user Tweeted from.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
text	text		The content of the Tweet.
urls	jsonb		List of URLs (e.g. https://steampipe.io) mentioned in the Tweet.
user_id	text	=	ID of the user the tweets are related to.
withheld	jsonb		Contains withholding details for withheld content.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- twitter

You can pass the configuration to the command with the --config argument:

steampipe_export_twitter --config '<your_config>' twitter_user_mention