judell

q1

   select
          by,
          sum(score) as sum_score
        from
          hn
        where
          time >= now() - interval '7 days'

judell

# duckdb

with names as (
  select
    unnest(string_to_array(?, ',')) as name
),

# sqlite

WITH RECURSIVE names(name, remaining) AS (

judell

locals {
  joined_companies = join(",", local.companies)
  joined_languages = join(",", local.languages)
  joined_operating_systems = join(",", local.operating_systems)
  joined_clouds = join(",", local.clouds)
  joined_dbs = join(",", local.dbs)
  joined_editors = join(",", local.editors)
}

judell

query "mentions" {
  sql = <<EOQ
    with names as (
      select
        unnest( $1::text[] ) as name
    ),
    counts as (
      select
        name,
        (

judell

  languages = [
    "C#",
    "C\\+\\+",
    "Clojure",
    "CSS",
    "Erlang",
    "golang| go 1.| (in|with|using) go | go (.+)(compiler|template|monorepo|generic|interface|library|framework|garbage|module|range|source)",
    "Haskell",
    "HTML",
    "Java ",

judell

  container {

    chart {
      base = chart.languages_base
      width = 4
      type = "donut"
      title = "language mentions: last 24 hours"
      query = query.mentions
      args = [ local.languages, 0, 1440 ]
    }

judell

def create_calendar_weeks(year, month, grouped_events):
    cal = calendar.monthcalendar(year, month)
    calendar_weeks = []
    
    for week in cal:
        calendar_week = []
        for day in week:
            if day == 0:
                calendar_week.append((0, []))

judell

You are a SQL tutor who helps people write Steampipe queries that involve JSON columns. Such queries can be hard to understand, so we want to provide queries in two forms: concise and expanded.

For example, we want a query to count my gists by language.

Here is the schema for the github_my_gist table

ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
comments	bigint		The number of comments for the gist.
created_at	timestamp with time zone		The timestamp when the gist was created.

judell

connection "all_wordpress" {
  plugin = "wordpress"
  type = "aggregator"
  connections = ["jon", "newstack"]
}

connection "jon" {
  plugin = "wordpress"
  endpoint = "https://blog.jonudell.net/wp-json/"
  username = "..."

judell

func listPosts(ctx context.Context, d *plugin.QueryData, _ *plugin.HydrateData) (interface{}, error) {
	conn, err := connect(ctx, d)
	if err != nil {
		return nil, err
	}

	plugin.Logger(ctx).Debug("WordPress listPosts author", "author", d.Quals["author"])
	plugin.Logger(ctx).Debug("WordPress listPosts date", "date", d.Quals["date"])

	options := &wordpress.PostListOptions{}