Dashboards visualization with PPL query

OpenSearch Dashboards
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):
2.11, 2.12 on AWS

Describe the issue:
I want to create a visualization to get the total sessions without eventname=‘click’ from a json log, using PPL.
Here is the log:

[
  {
    "date": "2024-01-01T07:36:00.908Z",
    "sessionid": "1",
    "eventname": "view"
  },
  {
    "date": "2024-01-01T06:36:00.908Z",
    "sessionid": "2",
    "eventname": "view"
  }, 
  {
    "date": "2024-01-01T07:30:00.908Z",
    "sessionid": "1",
    "eventname": "click"
  },
  {
    "date": "2024-01-01T06:38:00.908Z",
    "sessionid": "3",
    "eventname": "login"
  },
  {
    "date": "2024-01-01T06:39:00.908Z",
    "sessionid": "3",
    "eventname": "click"
  }, 
  {
    "date": "2024-01-01T06:38:00.908Z",
    "sessionid": "3",
    "eventname": "click"
  }
]

Expected result: 1 (sessionid=2)
search source=log | sort date | where eventname='click' | dedup sessionid | stats count() as count_click
I want to get total dedup sessionid as total, then total - count_click, how to pipeline these actions? Is there a way to create complex queries with join, subqueries or several WHERE syntax? Thanks a lot.

Can I use SQL in visualization, or import SQL results as source? The same question for opensearchsql plugin.

Configuration:

Relevant Logs or Screenshots:

2

I tried Query Workbench, the following SQL is invalid

SELECT DISTINCT sessionid   
FROM client_logs   
WHERE sessionid NOT IN (
    SELECT sessionid   
    FROM client_logs   
    WHERE eventname = 'click'
);

If I remove NOT, the query is valid, but the result is not correct. Has the problem been resolved in a new version?