DEV Community: Armaan Khan

bbb

Armaan Khan — Thu, 21 Aug 2025 13:21:40 +0000

 const renderCurrentView = () => {
    if (loading) return <LoadingSpinner />;

    switch (currentView) {
      case 'warehouses':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-blue-600 to-purple-600 rounded-2xl shadow-lg">
                  <Database className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    Warehouse Analytics
                  </h2>
                  <p className="text-gray-600 mt-1">Comprehensive performance monitoring</p>
                </div>
              </div>
              <div className="flex gap-4">
                <button
                  onClick={() => navigate('users')}
                  className="px-6 py-3 bg-gradient-to-r from-purple-600 to-pink-600 text-white rounded-2xl hover:from-purple-700 hover:to-pink-700 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
                >
                  <Users size={18} />
                  <span className="font-semibold">View Users</span>
                </button>
                <button
                  onClick={() => navigate('database')}
                  className="px-6 py-3 bg-gradient-to-r from-emerald-600 to-teal-600 text-white rounded-2xl hover:from-emerald-700 hover:to-teal-700 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
                >
                  <Database size={18} />
                  <span className="font-semibold">View Databases</span>
                </button>
              </div>
            </div>
            <DataTable
              data={warehousesData}
              columns={null}
              groupedColumns={warehouseGroupedColumns}
              onCellClick={handleWarehouseKPIClick}
              title="Advanced Warehouse Performance Analytics"
              searchable={true}
              fixedColumns={['WAREHOUSE_NAME']}
            />
          </div>
        );

      case 'users':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-purple-600 to-pink-600 rounded-2xl shadow-lg">
                  <Users className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    User Analytics
                  </h2>
                  <p className="text-gray-600 mt-1">Deep dive into user behavior patterns</p>
                </div>
              </div>
              <button
                onClick={() => navigate('warehouses')}
                className="px-6 py-3 bg-gradient-to-r from-blue-600 to-purple-600 text-white rounded-2xl hover:from-blue-700 hover:to-purple-700 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
              >
                <Database size={18} />
                <span className="font-semibold">View Warehouses</span>
              </button>
            </div>
            <DataTable
              data={usersData}
              columns={userColumns}
              onCellClick={handleUserKPIClick}
              title="User Performance Analysis"
            />
          </div>
        );

      case 'warehouse-drill':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-indigo-600 to-blue-600 rounded-2xl shadow-lg">
                  <BarChart3 className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    {selectedWarehouse} - {selectedKPI} Breakdown
                  </h2>
                  <p className="text-gray-600 mt-1">Detailed performance analysis</p>
                </div>
              </div>
              <button
                onClick={() => navigate('warehouses')}
                className="px-6 py-3 bg-gradient-to-r from-gray-600 to-gray-700 text-white rounded-2xl hover:from-gray-700 hover:to-gray-800 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
              >
                <ArrowLeft size={18} />
                <span className="font-semibold">Back to Warehouses</span>
              </button>
            </div>
            <DataTable
              data={drillDownData}
              columns={drillDownColumns}
              title={`Queries with ${selectedKPI} in ${selectedWarehouse}`}
            />
          </div>
        );

      case 'user-queries':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-green-600 to-teal-600 rounded-2xl shadow-lg">
                  <Activity className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    {selectedUser} - {selectedKPI} Queries
                  </h2>
                  <p className="text-gray-600 mt-1">Individual query performance metrics</p>
                </div>
              </div>
              <button
                onClick={() => window.history.back()}
                className="px-6 py-3 bg-gradient-to-r from-gray-600 to-gray-700 text-white rounded-2xl hover:from-gray-700 hover:to-gray-800 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
              >
                <ArrowLeft size={18} />
                <span className="font-semibold">Back</span>
              </button>
            </div>
            <DataTable
              data={queriesData}
              columns={queryColumns}
              title={`Query Details for ${selectedUser} (${selectedKPI})`}
            />
          </div>
        );

      case 'query-details':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-emerald-600 to-green-600 rounded-2xl shadow-lg">
                  <Activity className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    Query Details
                  </h2>
                  <p className="text-gray-600 mt-1">Comprehensive query analysis</p>
                </div>
              </div>
              <button
                onClick={() => window.history.back()}
                className="px-6 py-3 bg-gradient-to-r from-gray-600 to-gray-700 text-white rounded-2xl hover:from-gray-700 hover:to-gray-800 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
              >
                <ArrowLeft size={18} />
                <span className="font-semibold">Back</span>
              </button>
            </div>

            {queryDetails && (
              <div className="bg-white/90 backdrop-blur-xl rounded-3xl shadow-2xl border border-white/30 overflow-hidden relative">
                <div className="absolute inset-0 bg-gradient-to-br from-blue-50/30 via-transparent to-purple-50/30 pointer-events-none"></div>

                <div className="relative z-10 p-8">
                  <div className="grid grid-cols-1 lg:grid-cols-2 gap-8">
                    {/* Basic Information */}
                    <div className="space-y-6 p-6 bg-white/50 backdrop-blur rounded-2xl border border-white/20 shadow-lg">
                      <div className="flex items-center gap-3 pb-4 border-b border-gray-200">
                        <div className="p-2 bg-gradient-to-br from-blue-600 to-purple-600 rounded-xl">
                          <Database className="h-5 w-5 text-white" />
                        </div>
                        <h3 className="text-xl font-bold text-gray-900">Basic Information</h3>
                      </div>
                      <div className="space-y-4">
                        <div className="flex flex-col">
                          <label className="text-sm font-semibold text-gray-600 mb-1">Query ID</label>
                          <div className="p-3 bg-gray-50 rounded-xl font-mono text-sm text-gray-800 break-all">
                            {queryDetails.QUERY_ID}
                          </div>
                        </div>
                        <div className="grid grid-cols-2 gap-4">
                          <div>
                            <label className="text-sm font-semibold text-gray-600">User</label>
                            <div className="p-2 bg-blue-50 rounded-lg text-blue-800 font-medium mt-1">
                              {queryDetails.USER_NAME}
                            </div>
                          </div>
                          <div>
                            <label className="text-sm font-semibold text-gray-600">Warehouse</label>
                            <div className="p-2 bg-purple-50 rounded-lg text-purple-800 font-medium mt-1">
                              {queryDetails.WAREHOUSE_NAME}
                            </div>
                          </div>
                        </div>
                        <div className="grid grid-cols-2 gap-4">
                          <div>
                            <label className="text-sm font-semibold text-gray-600">Type</label>
                            <div className="p-2 bg-indigo-50 rounded-lg text-indigo-800 font-medium mt-1">
                              {queryDetails.QUERY_TYPE}
                            </div>
                          </div>
                          <div>
                            <label className="text-sm font-semibold text-gray-600">Status</label>
                            <div className={`p-2 rounded-lg font-medium mt-1 ${
                              queryDetails.EXECUTION_STATUS === 'SUCCESS' 
                                ? 'bg-green-50 text-green-800' 
                                : 'bg-red-50 text-red-800'
                            }`}>
                              {queryDetails.EXECUTION_STATUS}
                            </div>
                          </div>
                        </div>
                      </div>
                    </div>

                    {/* Performance Metrics */}
                    <div className="space-y-6 p-6 bg-white/50 backdrop-blur rounded-2xl border border-white/20 shadow-lg">
                      <div className="flex items-center gap-3 pb-4 border-b border-gray-200">
                        <div className="p-2 bg-gradient-to-br from-green-600 to-teal-600 rounded-xl">
                          <TrendingUp className="h-5 w-5 text-white" />
                        </div>
                        <h3 className="text-xl font-bold text-gray-900">Performance Metrics</h3>
                      </div>
                      <div className="grid grid-cols-2 gap-4">
                        <div className="p-4 bg-gradient-to-br from-blue-50 to-blue-100 rounded-xl">
                          <div className="flex items-center gap-2 mb-2">
                            <Clock className="h-4 w-4 text-blue-600" />
                            <span className="text-sm font-semibold text-blue-800">Duration</span>
                          </div>
                          <div className="text-lg font-bold text-blue-900">
                            {Number(queryDetails.TOTAL_ELAPSED_TIME_SECONDS || 0).toFixed(2)}s
                          </div>
                        </div>
                        <div className="p-4 bg-gradient-to-br from-purple-50 to-purple-100 rounded-xl">
                          <div className="flex items-center gap-2 mb-2">
                            <Zap className="h-4 w-4 text-purple-600" />
                            <span className="text-sm font-semibold text-purple-800">Execution</span>
                          </div>
                          <div className="text-lg font-bold text-purple-900">
                            {Number(queryDetails.EXECUTION_TIME_SECONDS || 0).toFixed(2)}s
                          </div>
                        </div>
                        <div className="p-4 bg-gradient-to-br from-green-50 to-green-100 rounded-xl">
                          <div className="flex items-center gap-2 mb-2">
                            <Activity className="h-4 w-4 text-green-600" />
                            <span className="text-sm font-semibold text-green-800">Compilation</span>
                          </div>
                          <div className="text-lg font-bold text-green-900">
                            {Number(queryDetails.COMPILATION_TIME_SECONDS || 0).toFixed(2)}s
                          </div>
                        </div>
                        <div className="p-4 bg-gradient-to-br from-yellow-50 to-yellow-100 rounded-xl">
                          <div className="flex items-center gap-2 mb-2">
                            <Database className="h-4 w-4 text-yellow-600" />
                            <span className="text-sm font-semibold text-yellow-800">Credits</span>
                          </div>
                          <div className="text-lg font-bold text-yellow-900">
                            {Number(queryDetails.CREDITS_USED_CLOUD_SERVICES || 0).toFixed(6)}
                          </div>
                        </div>
                      </div>
                      <div className="grid grid-cols-1 gap-4">
                        <div className="p-4 bg-gradient-to-r from-indigo-50 to-cyan-50 rounded-xl">
                          <div className="flex justify-between items-center">
                            <span className="text-sm font-semibold text-indigo-800">Bytes Scanned</span>
                            <span className="text-lg font-bold text-indigo-900">
                              {Number(queryDetails.BYTES_SCANNED_GB || 0).toFixed(2)} GB
                            </span>
                          </div>
                        </div>
                        <div className="p-4 bg-gradient-to-r from-pink-50 to-rose-50 rounded-xl">
                          <div className="flex justify-between items-center">
                            <span className="text-sm font-semibold text-pink-800">Rows Produced</span>
                            <span className="text-lg font-bold text-pink-900">
                              {Number(queryDetails.ROWS_PRODUCED || 0).toLocaleString()}
                            </span>
                          </div>
                        </div>
                      </div>
                    </div>

                    {/* Query Text */}
                    <div className="lg:col-span-2 space-y-6 p-6 bg-white/50 backdrop-blur rounded-2xl border border-white/20 shadow-lg">
                      <div className="flex items-center gap-3 pb-4 border-b border-gray-200">
                        <div className="p-2 bg-gradient-to-br from-gray-600 to-gray-700 rounded-xl">
                          <Search className="h-5 w-5 text-white" />
                        </div>
                        <h3 className="text-xl font-bold text-gray-900">Query Text</h3>
                      </div>
                      <div className="bg-gray-900 p-6 rounded-2xl shadow-inner relative overflow-hidden">
                        <div className="absolute top-0 left-0 w-full h-1 bg-gradient-to-r from-blue-500 via-purple-500 to-pink-500"></div>
                        <pre className="text-sm text-gray-100 whitespace-pre-wrap overflow-x-auto font-mono leading-relaxed">
                          {queryDetails.QUERY_TEXT_SAMPLE || 'Query text not available'}
                        </pre>
                      </div>
                    </div>

                    {/* Error Information */}
                    {queryDetails.ERROR_MESSAGE && (
                      <div className="lg:col-span-2 space-y-6 p-6 bg-red-50/80 backdrop-blur rounded-2xl border border-red-200/50 shadow-lg">
                        <div className="flex items-center gap-3 pb-4 border-b border-red-200">
                          <div className="p-2 bg-red-600 rounded-xl">
                            <AlertTriangle className="h-5 w-5 text-white" />
                          </div>
                          <h3 className="text-xl font-bold text-red-900">Error Information</h3>
                        </div>
                        <div className="space-y-4">
                          <div>
                            <label className="text-sm font-semibold text-red-700">Error Code</label>
                            <div className="p-3 bg-red-100 rounded-lg font-mono text-red-900 mt-1">
                              {queryDetails.ERROR_CODE}
                            </div>
                          </div>
                          <div>
                            <label className="text-sm font-semibold text-red-700">Message</label>
                            <div className="p-3 bg-red-100 rounded-lg text-red-900 mt-1">
                              {queryDetails.ERROR_MESSAGE}
                            </div>
                          </div>
                        </div>
                      </div>
                    )}
                  </div>
                </div>
              </div>
            )}
          </div>
        );

      case 'database':
        return (
          <div className="space-y-8">
            <div className="flex items-center justify-between p-6 bg-white/80 backdrop-blur-xl rounded-3xl shadow-xl border border-white/20">
              <div className="flex items-center gap-4">
                <div className="p-3 bg-gradient-to-br from-emerald-600 to-teal-600 rounded-2xl shadow-lg">
                  <Database className="h-6 w-6 text-white" />
                </div>
                <div>
                  <h2 className="text-2xl font-bold bg-gradient-to-r from-gray-900 to-gray-700 bg-clip-text text-transparent">
                    Database Analytics
                  </h2>
                  <p className="text-gray-600 mt-1">Storage and optimization insights</p>
                </div>
              </div>
              <button
                                onClick={() => navigate('warehouses')}
                className="px-6 py-3 bg-gradient-to-r from-blue-600 to-purple-600 text-white rounded-2xl hover:from-blue-700 hover:to-purple-700 transition-all duration-300 flex items-center gap-3 shadow-lg hover:shadow-xl transform hover:scale-105"
              >
                <Database size={18} />
                <span className="font-semibold">View Warehouses</span>
              </button>
            </div>
            <DataTable
              data={databaseData}
              columns={databaseColumns}
              onCellClick={handleDatabaseTableClick}
              title="Database Performance Analysis"
            />
          </div>
        );

      default:
        return <ErrorMessage message="Unknown view" onRetry={() => navigate('warehouses')} />;
    }
  };

aa

Armaan Khan — Thu, 21 Aug 2025 13:20:36 +0000

// Enhanced DataTable Component with hierarchical headers and column fixing
// const DataTable = ({ data, columns, onCellClick, title, searchable = true, groupedColumns = null, fixedColumns = [] }) => {
//   const [searchTerm, setSearchTerm] = useState('');
//   const [sortConfig, setSortConfig] = useState({ key: null, direction: 'asc' });
//   const [columnSettings, setColumnSettings] = useState(false);
//   const [fixedCols, setFixedCols] = useState(fixedColumns);

//   if (!data || data.length === 0) {
//     return (
//       <div className="bg-white/90 backdrop-blur-xl rounded-3xl shadow-2xl border border-white/20 p-16 text-center relative overflow-hidden">
//         <div className="absolute inset-0 bg-gradient-to-br from-blue-50/60 to-purple-50/60"></div>
//         <div className="relative z-10">
//           <div className="w-24 h-24 bg-gradient-to-br from-slate-200 to-slate-300 rounded-full flex items-center justify-center mx-auto mb-8 shadow-xl">
//             <Database className="h-12 w-12 text-slate-500" />
//           </div>
//           <h3 className="text-2xl font-bold text-slate-800 mb-3">No Data Available</h3>
//           <p className="text-slate-500 text-lg">Check back later or adjust your filters</p>
//         </div>
//       </div>
//     );
//   }

//   const filteredData = searchable ? data?.filter(row =>
//     Object.values(row).some(value =>
//       String(value).toLowerCase().includes(searchTerm.toLowerCase())
//     )
//   ) : data;

//   const sortedData = sortConfig.key
//     ? [...filteredData].sort((a, b) => {
//         if (a[sortConfig.key] < b[sortConfig.key]) {
//           return sortConfig.direction === 'asc' ? -1 : 1;
//         }
//         if (a[sortConfig.key] > b[sortConfig.key]) {
//           return sortConfig.direction === 'asc' ? 1 : -1;
//         }
//         return 0;
//       })
//     : filteredData;

//   const handleSort = (key) => {
//     setSortConfig({
//       key,
//       direction: sortConfig.key === key && sortConfig.direction === 'asc' ? 'desc' : 'asc'
//     });
//   };

//   const toggleColumnFixed = (columnKey) => {
//     setFixedCols(prev => 
//       prev.includes(columnKey) 
//         ? prev.filter(key => key !== columnKey)
//         : [...prev, columnKey]
//     );
//   };

//   const isColumnFixed = (columnKey) => fixedCols.includes(columnKey);

//   // Calculate fixed columns width
//   const getFixedColumnsWidth = () => {
//     const allColumns = groupedColumns ? groupedColumns.flatMap(g => g.columns) : columns;
//     return fixedCols.reduce((width, colKey) => {
//       const col = allColumns.find(c => c.key === colKey);
//       return width + (col?.width || 150); // default width
//     }, 0);
//   };

//   // Column Settings Panel
//   const ColumnSettingsPanel = () => {
//     const allColumns = groupedColumns ? groupedColumns.flatMap(g => g.columns) : columns;

//     return (
//       <div className={`fixed top-full right-0 mt-2 bg-white/95 backdrop-blur-xl rounded-2xl shadow-2xl border border-white/30 p-6 w-80 z-50 transform transition-all duration-300 ${columnSettings ? 'scale-100 opacity-100' : 'scale-95 opacity-0 pointer-events-none'}`}>
//         <div className="flex items-center justify-between mb-4">
//           <h4 className="text-lg font-bold text-slate-800">Column Settings</h4>
//           <button
//             onClick={() => setColumnSettings(false)}
//             className="p-2 hover:bg-slate-100 rounded-xl transition-colors"
//           >
//             <XCircle className="h-5 w-5 text-slate-500" />
//           </button>
//         </div>

//         <div className="space-y-3 max-h-96 overflow-y-auto">
//           {allColumns.map(col => (
//             <div key={col.key} className="flex items-center justify-between p-3 bg-slate-50/50 rounded-xl hover:bg-slate-100/50 transition-colors">
//               <div className="flex-1">
//                 <div className="font-medium text-slate-700">{col.title}</div>
//                 <div className="text-sm text-slate-500">{col.key}</div>
//               </div>
//               <button
//                 onClick={() => toggleColumnFixed(col.key)}
//                 className={`flex items-center gap-2 px-3 py-2 rounded-lg text-sm font-medium transition-all duration-200 ${
//                   isColumnFixed(col.key)
//                     ? 'bg-blue-500 text-white shadow-lg'
//                     : 'bg-white border border-slate-200 text-slate-600 hover:bg-blue-50 hover:border-blue-200'
//                 }`}
//               >
//                 {isColumnFixed(col.key) ? (
//                   <>
//                     <CheckCircle className="h-4 w-4" />
//                     Fixed
//                   </>
//                 ) : (
//                   <>
//                     <Database className="h-4 w-4" />
//                     Fix
//                   </>
//                 )}
//               </button>
//             </div>
//           ))}
//         </div>
//       </div>
//     );
//   };

//   // Render regular table headers
//   const renderRegularHeaders = () => (
//     <tr className="bg-gradient-to-r from-slate-100/95 to-slate-200/95 backdrop-blur-sm">
//       {columns.map(col => (
//         <th
//           key={col.key}
//           className={`px-6 py-5 text-left text-sm font-bold text-slate-700 uppercase tracking-wider border-r border-white/50 last:border-r-0 transition-all duration-200 ${
//             isColumnFixed(col.key) 
//               ? 'sticky left-0 bg-gradient-to-r from-blue-100/95 to-indigo-100/95 z-20 shadow-lg' 
//               : ''
//           } ${
//             col.sortable ? 'cursor-pointer hover:bg-slate-200/70 group' : ''
//           }`}
//           onClick={() => col.sortable && handleSort(col.key)}
//           style={isColumnFixed(col.key) ? { left: `${getFixedColumnsWidth() - (col.width || 150)}px` } : {}}
//         >
//           <div className="flex items-center gap-3">
//             <span className="group-hover:text-blue-600 transition-colors">{col.title}</span>
//             {isColumnFixed(col.key) && (
//               <div className="w-2 h-2 bg-blue-500 rounded-full animate-pulse"></div>
//             )}
//             {col.sortable && (
//               <ChevronRight 
//                 className={`h-4 w-4 transition-all duration-300 ${
//                   sortConfig.key === col.key 
//                     ? sortConfig.direction === 'desc' ? 'rotate-90 text-blue-600' : '-rotate-90 text-blue-600'
//                     : 'opacity-0 group-hover:opacity-70'
//                 }`} 
//               />
//             )}
//           </div>
//         </th>
//       ))}
//     </tr>
//   );

//   // Render grouped headers for warehouse table
//   const renderGroupedHeaders = () => {
//     return (
//       <>
//         {/* Main group headers */}
//         <tr className="bg-gradient-to-r from-blue-700 to-purple-700 text-white">
//           {groupedColumns.map((group, idx) => (
//             <th 
//               key={idx}
//               colSpan={group.columns.length} 
//               className="px-6 py-6 text-center text-sm font-bold uppercase tracking-wider border-r border-white/30 last:border-r-0 relative overflow-hidden"
//             >
//               <div className="absolute inset-0 bg-gradient-to-br from-white/10 to-transparent"></div>
//               <div className="relative flex items-center justify-center gap-3">
//                 {group.icon && (
//                   <div className="p-2 bg-white/20 rounded-xl">
//                     <group.icon className="h-5 w-5" />
//                   </div>
//                 )}
//                 <span className="text-lg font-extrabold">{group.title}</span>
//               </div>
//             </th>
//           ))}
//         </tr>

//         {/* Sub headers */}
//         <tr className="bg-gradient-to-r from-slate-50/98 to-slate-100/98 backdrop-blur-sm border-b-2 border-slate-200">
//           {groupedColumns.map(group => 
//             group.columns.map(col => {
//               const fixedLeft = isColumnFixed(col.key) ? 
//                 fixedCols.slice(0, fixedCols.indexOf(col.key))
//                   .reduce((acc, key) => acc + 150, 0) : 0;

//               return (
//                 <th
//                   key={col.key}
//                   className={`px-4 py-4 text-left text-xs font-bold text-slate-700 uppercase tracking-wider border-r border-slate-200/50 last:border-r-0 transition-all duration-200 ${
//                     isColumnFixed(col.key) 
//                       ? 'sticky left-0 bg-gradient-to-r from-blue-50/98 to-indigo-50/98 z-20 shadow-lg border-r-2 border-blue-200' 
//                       : ''
//                   } ${
//                     col.sortable ? 'cursor-pointer hover:bg-slate-200/60 group' : ''
//                   }`}
//                   onClick={() => col.sortable && handleSort(col.key)}
//                   style={isColumnFixed(col.key) ? { left: `${fixedLeft}px` } : {}}
//                 >
//                   <div className="flex items-center gap-2">
//                     <span className={`group-hover:text-blue-600 transition-colors font-semibold ${col.clickable ? 'text-blue-600' : ''}`}>
//                       {col.title}
//                     </span>
//                     {isColumnFixed(col.key) && (
//                       <div className="w-1.5 h-1.5 bg-blue-500 rounded-full animate-pulse"></div>
//                     )}
//                     {col.sortable && (
//                       <ChevronRight 
//                         className={`h-3 w-3 transition-all duration-300 ${
//                           sortConfig.key === col.key 
//                             ? sortConfig.direction === 'desc' ? 'rotate-90 text-blue-600' : '-rotate-90 text-blue-600'
//                             : 'opacity-0 group-hover:opacity-70'
//                         }`} 
//                       />
//                     )}
//                   </div>
//                 </th>
//               );
//             })
//           )}
//         </tr>
//       </>
//     );
//   };

//   return (
//     <div className="bg-white/95 backdrop-blur-2xl rounded-3xl shadow-2xl border border-white/40 overflow-hidden relative">
//       {/* Enhanced gradient overlay */}
//       <div className="absolute inset-0 bg-gradient-to-br from-blue-50/40 via-transparent to-purple-50/40 pointer-events-none"></div>

//       {/* Enhanced Header */}
//       <div className="relative z-10 p-8 bg-gradient-to-r from-slate-50/90 to-slate-100/90 backdrop-blur-sm border-b-2 border-white/30">
//         <div className="flex items-center justify-between">
//           <div className="flex items-center gap-6">
//             <div className="p-4 bg-gradient-to-br from-blue-600 to-purple-600 rounded-2xl shadow-xl">
//               <BarChart3 className="h-8 w-8 text-white" />
//             </div>
//             <div>
//               <h3 className="text-2xl font-bold bg-gradient-to-r from-slate-900 to-slate-700 bg-clip-text text-transparent">
//                 {title}
//               </h3>
//               <p className="text-slate-500 mt-1 text-lg">Advanced interactive data analysis</p>
//             </div>
//           </div>

//           <div className="flex items-center gap-4">
//             {/* Column Settings Button */}
//             <div className="relative">
//               <button
//                 onClick={() => setColumnSettings(!columnSettings)}
//                 className="flex items-center gap-2 px-4 py-3 bg-white/80 backdrop-blur border border-slate-200 rounded-xl hover:bg-blue-50 hover:border-blue-300 transition-all duration-300 shadow-lg hover:shadow-xl group"
//               >
//                 <Database className="h-5 w-5 text-slate-600 group-hover:text-blue-600 transition-colors" />
//                 <span className="text-sm font-medium text-slate-700 group-hover:text-blue-700">Column Settings</span>
//               </button>
//               <ColumnSettingsPanel />
//             </div>

//             {/* Enhanced Search */}
//             {searchable && (
//               <div className="relative group">
//                 <Search className="h-5 w-5 absolute left-4 top-1/2 transform -translate-y-1/2 text-slate-400 group-focus-within:text-blue-600 transition-colors" />
//                 <input
//                   type="text"
//                   placeholder="Search across all data..."
//                   value={searchTerm}
//                   onChange={(e) => setSearchTerm(e.target.value)}
//                   className="pl-12 pr-6 py-3 w-96 bg-white/90 backdrop-blur-sm border border-slate-200 rounded-xl focus:ring-4 focus:ring-blue-500/20 focus:border-blue-400 transition-all duration-300 shadow-lg hover:shadow-xl text-slate-700 placeholder-slate-400"
//                 />
//                 <div className="absolute inset-0 bg-gradient-to-r from-blue-500/5 to-purple-500/5 rounded-xl opacity-0 group-focus-within:opacity-100 transition-opacity duration-300 pointer-events-none"></div>
//               </div>
//             )}
//           </div>
//         </div>

//         {/* Fixed columns indicator */}
//         {fixedCols.length > 0 && (
//           <div className="mt-4 flex items-center gap-2">
//             <div className="flex items-center gap-2 px-3 py-2 bg-blue-100/80 rounded-xl">
//               <CheckCircle className="h-4 w-4 text-blue-600" />
//               <span className="text-sm font-medium text-blue-800">{fixedCols.length} Fixed Column{fixedCols.length > 1 ? 's' : ''}</span>
//             </div>
//           </div>
//         )}
//       </div>

//       {/* Enhanced Table */}
//       <div className="relative z-10 overflow-x-auto">
//         <table className="w-full">
//           <thead>
//             {groupedColumns ? renderGroupedHeaders() : renderRegularHeaders()}
//           </thead>
//           <tbody className="divide-y divide-slate-100/80">
//             {sortedData.map((row, idx) => (
//               <tr key={idx} className="hover:bg-gradient-to-r hover:from-blue-50/60 hover:to-purple-50/60 transition-all duration-300 group relative">
//                 {(groupedColumns ? groupedColumns.flatMap(g => g.columns) : columns).map(col => {
//                   const fixedLeft = isColumnFixed(col.key) ? 
//                     fixedCols.slice(0, fixedCols.indexOf(col.key))
//                       .reduce((acc, key) => acc + 150, 0) : 0;

//                   return (
//                     <td
//                       key={col.key}
//                       className={`px-4 py-5 whitespace-nowrap text-sm border-r border-slate-100/60 last:border-r-0 relative transition-all duration-200 ${
//                         isColumnFixed(col.key) 
//                           ? 'sticky left-0 bg-gradient-to-r from-blue-50/90 to-indigo-50/90 z-10 shadow-lg border-r-2 border-blue-200' 
//                           : ''
//                       } ${
//                         col.clickable 
//                           ? 'text-blue-600 hover:text-blue-800 cursor-pointer font-semibold hover:bg-blue-50/40 transition-all duration-300' 
//                           : col.fixed 
//                             ? 'text-slate-900 font-bold bg-slate-50/60' 
//                             : 'text-slate-700'
//                       }`}
//                       onClick={() => col.clickable && onCellClick && onCellClick(row, col)}
//                       style={isColumnFixed(col.key) ? { left: `${fixedLeft}px` } : {}}
//                     >
//                       {col.clickable && (
//                         <div className="absolute inset-0 bg-gradient-to-r from-blue-100/0 via-blue-100/40 to-blue-100/0 transform scale-x-0 group-hover:scale-x-100 transition-transform duration-300 origin-left"></div>
//                       )}
//                       <span className="relative z-10 font-medium">
//                         {col.render ? col.render(row[col.key], row) : (row[col.key] || 0)}
//                       </span>
//                       {col.clickable && (
//                         <div className="absolute right-3 top-1/2 transform -translate-y-1/2 opacity-0 group-hover:opacity-100 transition-opacity duration-200">
//                           <ChevronRight className="h-3 w-3 text-blue-500" />
//                         </div>
//                       )}
//                     </td>
//                   );
//                 })}
//               </tr>
//             ))}
//           </tbody>
//         </table>
//       </div>

//       {/* Enhanced Footer */}
//       <div className="relative z-10 px-8 py-6 bg-gradient-to-r from-slate-50/90 to-slate-100/90 backdrop-blur-sm border-t-2 border-white/30">
//         <div className="flex items-center justify-between">
//           <div className="flex items-center gap-6">
//             <div className="text-sm text-slate-600">
//               Showing <span className="font-bold text-slate-900 text-lg">{sortedData.length}</span> of <span className="font-bold text-slate-900 text-lg">{data.length}</span> records
//             </div>
//             {searchTerm && (
//               <div className="flex items-center gap-2 px-3 py-1 bg-blue-100/60 rounded-full">
//                 <Search className="h-3 w-3 text-blue-600" />
//                 <span className="text-xs font-medium text-blue-700">Filtered results</span>
//               </div>
//             )}
//           </div>
//           <div className="flex items-center gap-4">
//             <div className="flex items-center gap-2 text-sm text-slate-500">
//               <div className="w-2 h-2 bg-emerald-400 rounded-full animate-pulse shadow-lg"></div>
//               <span className="font-medium">Real-time data</span>
//             </div>
//             <div className="text-xs text-slate-400">
//               Last updated: {new Date().toLocaleTimeString()}
//             </div>
//           </div>
//         </div>
//       </div>
//     </div>
//   );
// };

Queries

Armaan Khan — Fri, 08 Aug 2025 03:21:00 +0000

for exmaple you have all this . you have just extarcted in ptrhon . i have give you the query and you have extrct all data of table . now for warehouse based on queriy id . can you make one function which take for exampel on warehouse table i show in react and i click on spilled to remote qeures . so what i want you have to wrie a fctuon whihc take col name and than fromw arehouse queresi id col object take id of that parctul type of qurires and than called to query summary table and than basd on ids he  gropu by user and show a tabel whic show that yeah this is the user which have ran ths spilled qures and wihtt count.


 . if i pass the query id array  to this . so can i have a fctuon whic take output of this queyr as a df, and query id . and htan based on that give me resutl with gropb by user that this user has ran 24 of t and than other user rna with id . liek a df it will returnn is . user name , query count , and array of all the querd id for that suer . [this colmn will be hidden ] . and than one fucuotn whirh take the id and show the query etxt , and id in tbale and one button which is click to view full detals and wehn click on that so we pass the id to that fuction 
-- =====================================================
-- TABLE 1: QUERY_HISTORY_SUMMARY 
-- This table provides all query overview data for drilling down
-- =====================================================

CREATE OR REPLACE TABLE QUERY_HISTORY_SUMMARY AS
SELECT 
    -- Query Identification
    q.QUERY_ID,
    q.QUERY_HASH,
    q.QUERY_PARAMETERIZED_HASH,
    LEFT(q.QUERY_TEXT, 100) as QUERY_TEXT_PREVIEW,
    q.QUERY_TYPE,
    q.QUERY_TAG,

    -- Timing Information
    q.START_TIME,
    q.END_TIME,
    q.TOTAL_ELAPSED_TIME,
    q.COMPILATION_TIME,
    q.EXECUTION_TIME,

    -- User and Session Info
    q.USER_NAME,
    q.USER_TYPE,
    q.ROLE_NAME,
    q.ROLE_TYPE,
    q.SESSION_ID,

    -- Warehouse Information
    q.WAREHOUSE_ID,
    q.WAREHOUSE_NAME,
    q.WAREHOUSE_SIZE,
    q.WAREHOUSE_TYPE,
    q.CLUSTER_NUMBER,

    -- Database Context
    q.DATABASE_ID,
    q.DATABASE_NAME,
    q.SCHEMA_ID,
    q.SCHEMA_NAME,
    q.USER_DATABASE_NAME,
    q.USER_SCHEMA_NAME,

    -- Execution Status
    q.EXECUTION_STATUS,
    q.ERROR_CODE,
    LEFT(q.ERROR_MESSAGE, 200) as ERROR_MESSAGE_PREVIEW,

    -- Performance Metrics
    q.BYTES_SCANNED,
    q.PERCENTAGE_SCANNED_FROM_CACHE,
    q.BYTES_WRITTEN,
    q.ROWS_PRODUCED,
    q.ROWS_INSERTED,
    q.ROWS_UPDATED,
    q.ROWS_DELETED,

    -- Resource Usage
    q.CREDITS_USED_CLOUD_SERVICES,
    q.BYTES_SPILLED_TO_LOCAL_STORAGE,
    q.BYTES_SPILLED_TO_REMOTE_STORAGE,
    q.PARTITIONS_SCANNED,
    q.PARTITIONS_TOTAL,

    -- Queue Times
    q.QUEUED_PROVISIONING_TIME,
    q.QUEUED_REPAIR_TIME,
    q.QUEUED_OVERLOAD_TIME,
    q.TRANSACTION_BLOCKED_TIME,

    -- Classification Buckets for easy filtering
    CASE 
        WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
        WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds' 
        WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
        WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
        WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
        ELSE '5+ minutes'
    END as DURATION_BUCKET,

    CASE 
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
        ELSE '100+ cents'
    END as CREDIT_BUCKET,

    CASE 
        WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'SPILLED'
        ELSE 'NO_SPILL'
    END as SPILL_STATUS,

    CASE 
        WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) > 0 THEN 'QUEUED'
        ELSE 'NOT_QUEUED'
    END as QUEUE_STATUS,

    -- Analysis metadata
    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
    AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;

-- =====================================================
-- TABLE 2: QUERY_DETAILS_COMPLETE
-- This table provides complete details for any specific query
-- =====================================================

CREATE OR REPLACE TABLE QUERY_DETAILS_COMPLETE AS
WITH filtered_queries AS (
    SELECT
        q.*,
        -- Calculate partition scan percentage
        CASE
            WHEN q.PARTITIONS_TOTAL > 0 THEN
                ROUND((q.PARTITIONS_SCANNED::FLOAT / q.PARTITIONS_TOTAL::FLOAT) * 100, 2)
            ELSE 0
        END as PARTITION_SCAN_PERCENTAGE,

        -- Calculate compilation time percentage
        CASE
            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
                ROUND((q.COMPILATION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
            ELSE 0
        END as COMPILATION_TIME_PERCENTAGE,

        -- Calculate execution time percentage
        CASE
            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
                ROUND((q.EXECUTION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
            ELSE 0
        END as EXECUTION_TIME_PERCENTAGE,

        -- Calculate rows per MB scanned
        CASE
            WHEN q.BYTES_SCANNED > 0 THEN
                ROUND(q.ROWS_PRODUCED::FLOAT / (q.BYTES_SCANNED::FLOAT / 1024 / 1024), 2)
            ELSE 0
        END as ROWS_PER_MB_SCANNED,

        -- Classify performance
        CASE
            WHEN q.TOTAL_ELAPSED_TIME <= 1000 THEN 'VERY_FAST'
            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN 'FAST'
            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN 'MODERATE'
            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN 'SLOW'
            ELSE 'VERY_SLOW'
        END as PERFORMANCE_CATEGORY,

        -- Classify cache efficiency
        CASE
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 90 THEN 'HIGH_CACHE_HIT'
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 50 THEN 'MEDIUM_CACHE_HIT'
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE > 0 THEN 'LOW_CACHE_HIT'
            ELSE 'NO_CACHE_HIT'
        END as CACHE_EFFICIENCY,

        -- Classify spilling
        CASE
            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 AND q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'BOTH_SPILL'
            WHEN q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'REMOTE_SPILL'
            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 'LOCAL_SPILL'
            ELSE 'NO_SPILL'
        END as SPILL_CLASSIFICATION

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
    WHERE q.START_TIME >= CURRENT_DATE - 1
        AND q.QUERY_ID IS NOT NULL
        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')
        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')
)
SELECT 
    -- Core Query Information
    QUERY_ID,
    QUERY_TEXT,
    QUERY_HASH,
    QUERY_HASH_VERSION,
    QUERY_PARAMETERIZED_HASH,
    QUERY_PARAMETERIZED_HASH_VERSION,
    QUERY_TYPE,
    QUERY_TAG,

    -- Timing Details (all in milliseconds)
    START_TIME,
    END_TIME,
    TOTAL_ELAPSED_TIME,
    COMPILATION_TIME,
    EXECUTION_TIME,
    QUEUED_PROVISIONING_TIME,
    QUEUED_REPAIR_TIME,
    QUEUED_OVERLOAD_TIME,
    TRANSACTION_BLOCKED_TIME,
    CHILD_QUERIES_WAIT_TIME,
    QUERY_RETRY_TIME,
    QUERY_RETRY_CAUSE,
    FAULT_HANDLING_TIME,
    LIST_EXTERNAL_FILES_TIME,

    -- User and Authentication
    USER_NAME,
    USER_TYPE,
    ROLE_NAME,
    ROLE_TYPE,
    SECONDARY_ROLE_STATS,
    SESSION_ID,

    -- Warehouse and Compute
    WAREHOUSE_ID,
    WAREHOUSE_NAME,
    WAREHOUSE_SIZE,
    WAREHOUSE_TYPE,
    CLUSTER_NUMBER,
    QUERY_LOAD_PERCENT,

    -- Database Context
    DATABASE_ID,
    DATABASE_NAME,
    SCHEMA_ID,
    SCHEMA_NAME,
    USER_DATABASE_ID,
    USER_DATABASE_NAME,
    USER_SCHEMA_ID,
    USER_SCHEMA_NAME,

    -- Execution Results
    EXECUTION_STATUS,
    ERROR_CODE,
    ERROR_MESSAGE,
    IS_CLIENT_GENERATED_STATEMENT,

    -- Data Processing Metrics
    BYTES_SCANNED,
    PERCENTAGE_SCANNED_FROM_CACHE,
    BYTES_WRITTEN,
    BYTES_WRITTEN_TO_RESULT,
    BYTES_READ_FROM_RESULT,
    ROWS_PRODUCED,
    ROWS_WRITTEN_TO_RESULT,
    ROWS_INSERTED,
    ROWS_UPDATED,
    ROWS_DELETED,
    ROWS_UNLOADED,
    BYTES_DELETED,

    -- Partitioning
    PARTITIONS_SCANNED,
    PARTITIONS_TOTAL,
    PARTITION_SCAN_PERCENTAGE,

    -- Memory and Spilling
    BYTES_SPILLED_TO_LOCAL_STORAGE,
    BYTES_SPILLED_TO_REMOTE_STORAGE,
    BYTES_SENT_OVER_THE_NETWORK,

    -- Credits and Cost
    CREDITS_USED_CLOUD_SERVICES,

    -- Data Transfer
    OUTBOUND_DATA_TRANSFER_CLOUD,
    OUTBOUND_DATA_TRANSFER_REGION,
    OUTBOUND_DATA_TRANSFER_BYTES,
    INBOUND_DATA_TRANSFER_CLOUD,
    INBOUND_DATA_TRANSFER_REGION,
    INBOUND_DATA_TRANSFER_BYTES,

    -- External Functions
    EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,
    EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
    EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
    EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,
    EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,

    -- Query Acceleration
    QUERY_ACCELERATION_BYTES_SCANNED,
    QUERY_ACCELERATION_PARTITIONS_SCANNED,
    QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,

    -- Transaction Information
    TRANSACTION_ID,

    -- System Information
    RELEASE_VERSION,

    -- Performance Ratios and Calculated Fields
    COMPILATION_TIME_PERCENTAGE,
    EXECUTION_TIME_PERCENTAGE,
    ROWS_PER_MB_SCANNED,

    -- Performance Classifications
    PERFORMANCE_CATEGORY,
    CACHE_EFFICIENCY,
    SPILL_CLASSIFICATION,

    -- Analysis metadata
    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM filtered_queries
ORDER BY START_TIME DESC;

-- =====================================================
-- REFRESH PROCEDURES
-- =====================================================



-- Table 3 Warehouse 


CREATE OR REPLACE TABLE WAREHOUSE_ANALYTICS_DASHBOARD_with_queries AS
WITH warehouse_info AS (
    -- Get warehouse metadata
    SELECT DISTINCT
        wh.WAREHOUSE_ID,
        wh.WAREHOUSE_NAME,
        wh.SIZE,
        wh.WAREHOUSE_TYPE,
        wh.CLUSTER_COUNT,
        NULL as SUSPEND_POLICY,
        NULL as MIN_CLUSTER_COUNT,
        NULL as MAX_CLUSTER_COUNT
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY wh
    WHERE wh.TIMESTAMP >= CURRENT_DATE - 1
),

query_buckets AS (
    SELECT
        q.WAREHOUSE_ID,
        q.WAREHOUSE_NAME,
        q.QUERY_ID,
        q.TOTAL_ELAPSED_TIME,
        q.EXECUTION_STATUS,
        q.CREDITS_USED_CLOUD_SERVICES,
        q.BYTES_SPILLED_TO_LOCAL_STORAGE,
        q.BYTES_SPILLED_TO_REMOTE_STORAGE,
        q.QUERY_TYPE,

        CASE
            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
            ELSE '5+ minutes'
        END as DURATION_BUCKET,

        CASE
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 120000 THEN '1-2 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 300000 THEN '2-5 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 600000 THEN '5-10 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 1200000 THEN '10-20 minutes'
            ELSE '20+ minutes'
        END as QUEUED_BUCKET,

        CASE
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
            ELSE '100+ cents'
        END as CREDIT_UTILIZATION_BUCKET

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
    WHERE q.START_TIME >= CURRENT_DATE - 1
        AND q.WAREHOUSE_ID IS NOT NULL
        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')
        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')
        AND q.QUERY_TEXT IS NOT NULL
),

warehouse_metrics AS (
    SELECT
        wm.WAREHOUSE_ID,
        wm.WAREHOUSE_NAME,
        SUM(wm.CREDITS_USED) as TOTAL_CREDITS_USED,
        SUM(wm.CREDITS_USED_COMPUTE) as TOTAL_COMPUTE_CREDITS,
        SUM(wm.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CLOUD_SERVICES_CREDITS
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
    WHERE wm.START_TIME >= CURRENT_DATE - 1
    GROUP BY wm.WAREHOUSE_ID, wm.WAREHOUSE_NAME
)

SELECT
    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID) as WAREHOUSE_ID,
    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME) as WAREHOUSE_NAME,
    wi.SIZE as WAREHOUSE_SIZE,
    wi.WAREHOUSE_TYPE,
    wi.CLUSTER_COUNT,
    wi.SUSPEND_POLICY,
    wi.MIN_CLUSTER_COUNT,
    wi.MAX_CLUSTER_COUNT,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN 1 END) as QUERIES_1_10_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN 1 END) as QUERIES_10_20_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN 1 END) as QUERIES_20_60_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN 1 END) as QUERIES_1_3_MIN,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN 1 END) as QUERIES_3_5_MIN,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN 1 END) as QUERIES_5_PLUS_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN 1 END) as QUEUED_1_2_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN 1 END) as QUEUED_2_5_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN 1 END) as QUEUED_5_10_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN 1 END) as QUEUED_10_20_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN 1 END) as QUEUED_20_PLUS_MIN,

    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_LOCAL,
    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_REMOTE,
    SUM(qb.BYTES_SPILLED_TO_LOCAL_STORAGE) as TOTAL_BYTES_SPILLED_LOCAL,
    SUM(qb.BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_REMOTE,

    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN 1 END) as QUERIES_0_20_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN 1 END) as QUERIES_20_40_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN 1 END) as QUERIES_40_60_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN 1 END) as QUERIES_60_80_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN 1 END) as QUERIES_80_100_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN 1 END) as QUERIES_100_PLUS_CENTS,

    -- New column: query_ids
    OBJECT_CONSTRUCT(
        '1-10_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN qb.QUERY_ID END),
        '10-20_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN qb.QUERY_ID END),
        '20-60_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN qb.QUERY_ID END),
        '1-3_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN qb.QUERY_ID END),
        '3-5_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN qb.QUERY_ID END),
        '5_plus_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN qb.QUERY_ID END),
        'queued_1-2_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN qb.QUERY_ID END),
        'queued_2-5_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN qb.QUERY_ID END),
        'queued_5-10_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN qb.QUERY_ID END),
        'queued_10-20_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN qb.QUERY_ID END),
        'queued_20_plus_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN qb.QUERY_ID END),
        'spilled_local_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN qb.QUERY_ID END),
        'spilled_remote_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN qb.QUERY_ID END),
        'failed_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN qb.QUERY_ID END),
        'successful_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN qb.QUERY_ID END),
        'running_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN qb.QUERY_ID END),
        'credit_0-20_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN qb.QUERY_ID END),
        'credit_20-40_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN qb.QUERY_ID END),
        'credit_40-60_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN qb.QUERY_ID END),
        'credit_60-80_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN qb.QUERY_ID END),
        'credit_80-100_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN qb.QUERY_ID END),
        'credit_100_plus_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN qb.QUERY_ID END)
    ) as QUERY_IDS,

    COUNT(qb.QUERY_ID) as TOTAL_QUERIES,
    COALESCE(wm.TOTAL_CREDITS_USED, 0) as TOTAL_CREDITS_USED,
    COALESCE(wm.TOTAL_COMPUTE_CREDITS, 0) as TOTAL_COMPUTE_CREDITS,
    COALESCE(wm.TOTAL_CLOUD_SERVICES_CREDITS, 0) as TOTAL_CLOUD_SERVICES_CREDITS,

    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM warehouse_info wi
FULL OUTER JOIN query_buckets qb
    ON wi.WAREHOUSE_ID = qb.WAREHOUSE_ID
FULL OUTER JOIN warehouse_metrics wm
    ON COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID) = wm.WAREHOUSE_ID

GROUP BY
    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID),
    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME),
    wi.SIZE,
    wi.WAREHOUSE_TYPE,
    wi.CLUSTER_COUNT,
    wi.SUSPEND_POLICY,
    wi.MIN_CLUSTER_COUNT,
    wi.MAX_CLUSTER_COUNT,
    wm.TOTAL_CREDITS_USED,
    wm.TOTAL_COMPUTE_CREDITS,
    wm.TOTAL_CLOUD_SERVICES_CREDITS

ORDER BY TOTAL_QUERIES DESC;






-- table 4 user 


CREATE OR REPLACE TABLE user_query_performance_report AS
WITH percentile_reference AS (
    SELECT
        warehouse_size,
        PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY bytes_scanned) AS bytes_scanned_p10,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_elapsed_time) AS execution_time_p90
    FROM snowflake.account_usage.query_history
   WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_DATE)
  AND START_TIME <= CURRENT_DATE

      AND user_name IS NOT NULL
      AND query_type NOT IN ('DESCRIBE', 'SHOW', 'USE')
    GROUP BY warehouse_size
),
query_flags AS (
    SELECT
        qh.query_id,
        qh.warehouse_size,
        qh.bytes_scanned,
        qh.total_elapsed_time AS execution_time_ms,
        qh.compilation_time,
        qh.start_time,
        qh.query_text,
        qh.query_hash,
        qh.query_tag,
        qh.error_code,
        qh.execution_status,
        qh.partitions_scanned,
        qh.partitions_total,
        qh.bytes_spilled_to_local_storage,
        qh.bytes_spilled_to_remote_storage,
        qh.user_name,
        qh.database_name,
        qh.schema_name,
        qh.warehouse_name,
        COALESCE(qh.credits_used_cloud_services, 0) AS credits_used_cloud_services,
        qh.rows_produced,
        qh.rows_deleted,
        qh.rows_inserted,
        qh.rows_updated,
        CASE WHEN qh.warehouse_size IN ('MEDIUM', 'LARGE', 'X-LARGE', '2X-LARGE', '3X-LARGE', '4X-LARGE') AND qh.bytes_scanned < pr.bytes_scanned_p10 THEN 1 ELSE 0 END AS over_provisioned,
        CASE WHEN EXTRACT(HOUR FROM qh.start_time) BETWEEN 9 AND 17 AND qh.total_elapsed_time > 300000 THEN 1 ELSE 0 END AS peak_hour_long_running,
        CASE WHEN qh.query_text ILIKE 'SELECT *%' THEN 1 ELSE 0 END AS select_star,
        CASE WHEN qh.partitions_total > 0 AND qh.partitions_scanned = qh.partitions_total THEN 1 ELSE 0 END AS unpartitioned_scan,
        CASE WHEN qh.query_hash IS NOT NULL THEN 1 ELSE 0 END AS repeated_query,
        CASE WHEN (qh.query_text ILIKE '%JOIN%' AND qh.query_text ILIKE '%JOIN%') OR qh.query_text ILIKE '%WINDOW%' THEN 1 ELSE 0 END AS complex_query,
        CASE WHEN qh.error_code IS NOT NULL OR qh.execution_status IN ('FAILED', 'CANCELLED') THEN 1 ELSE 0 END AS failed_cancelled,
        CASE WHEN qh.bytes_spilled_to_local_storage > 0 OR qh.bytes_spilled_to_remote_storage > 0 THEN 1 ELSE 0 END AS spilled,
        CASE WHEN qh.rows_produced = 0 AND qh.bytes_scanned > 1000000 THEN 1 ELSE 0 END AS zero_result_query,
        CASE WHEN qh.compilation_time > 5000 THEN 1 ELSE 0 END AS high_compile_time,
        CASE WHEN qh.query_tag IS NULL THEN 1 ELSE 0 END AS untagged_query,
        CASE WHEN qh.query_text ILIKE '%ORDER BY%' AND qh.query_text NOT ILIKE '%LIMIT%' THEN 1 ELSE 0 END AS unlimited_order_by,
        CASE WHEN qh.query_text ILIKE '%GROUP BY%' AND qh.rows_produced > 1000000 THEN 1 ELSE 0 END AS large_group_by,
        CASE WHEN qh.total_elapsed_time > pr.execution_time_p90 THEN 1 ELSE 0 END AS slow_query,
        CASE WHEN qh.query_text ILIKE '%DISTINCT%' AND qh.bytes_scanned > 100000000 THEN 1 ELSE 0 END AS expensive_distinct,
        CASE WHEN qh.query_text ILIKE '%LIKE%' AND qh.query_text NOT ILIKE '%INDEX%' THEN 1 ELSE 0 END AS inefficient_like,
        CASE WHEN qh.bytes_scanned > 0 AND qh.rows_produced = 0 THEN 1 ELSE 0 END AS no_results_with_scan,
        CASE WHEN qh.query_text ILIKE '%CROSS JOIN%' OR (qh.query_text ILIKE '%JOIN%' AND qh.query_text NOT ILIKE '%ON%') THEN 1 ELSE 0 END AS cartesian_join,
        CASE WHEN qh.total_elapsed_time > 0 AND qh.compilation_time / qh.total_elapsed_time > 0.5 THEN 1 ELSE 0 END AS high_compile_ratio
    FROM snowflake.account_usage.query_history qh
    LEFT JOIN percentile_reference pr ON qh.warehouse_size = pr.warehouse_size
    WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_DATE)
  AND START_TIME <= CURRENT_DATE

        AND qh.query_type NOT IN ('DESCRIBE', 'SHOW', 'USE')
        AND qh.user_name IS NOT NULL
        AND qh.total_elapsed_time > 1000
),
sample_queries AS (
    SELECT
        user_name,
        'over_provisioned' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY start_time DESC) AS sample_queries
    FROM query_flags WHERE over_provisioned = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'over_provisioned', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(over_provisioned) = 0
    UNION ALL
    SELECT
        user_name,
        'peak_hour_long_running' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY execution_time_ms DESC) AS sample_queries
    FROM query_flags WHERE peak_hour_long_running = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'peak_hour_long_running', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(peak_hour_long_running) = 0
    UNION ALL
    SELECT
        user_name,
        'select_star' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE select_star = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'select_star', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(select_star) = 0
    UNION ALL
    SELECT
        user_name,
        'unpartitioned_scan' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'partitions_scanned', partitions_scanned, 'partitions_total', partitions_total, 'start_time', start_time)) WITHIN GROUP (ORDER BY partitions_scanned DESC) AS sample_queries
    FROM query_flags WHERE unpartitioned_scan = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'unpartitioned_scan', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(unpartitioned_scan) = 0
    UNION ALL
    SELECT
        user_name,
        'spilled' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_spilled_to_local_storage', bytes_spilled_to_local_storage, 'bytes_spilled_to_remote_storage', bytes_spilled_to_remote_storage, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY (bytes_spilled_to_local_storage + bytes_spilled_to_remote_storage) DESC) AS sample_queries
    FROM query_flags WHERE spilled = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'spilled', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(spilled) = 0
    UNION ALL
    SELECT
        user_name,
        'failed_cancelled' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'error_code', error_code, 'execution_status', execution_status, 'start_time', start_time)) WITHIN GROUP (ORDER BY start_time DESC) AS sample_queries
    FROM query_flags WHERE failed_cancelled = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'failed_cancelled', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(failed_cancelled) = 0
    UNION ALL
    SELECT
        user_name,
        'zero_result_query' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'bytes_scanned', bytes_scanned, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE zero_result_query = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'zero_result_query', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(zero_result_query) = 0
    UNION ALL
    SELECT
        user_name,
        'high_compile_time' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'compilation_time', compilation_time, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY compilation_time DESC) AS sample_queries
    FROM query_flags WHERE high_compile_time = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'high_compile_time', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(high_compile_time) = 0
    UNION ALL
    SELECT
        user_name,
        'slow_query' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY execution_time_ms DESC) AS sample_queries
    FROM query_flags WHERE slow_query = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'slow_query', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(slow_query) = 0
    UNION ALL
    SELECT
        user_name,
        'cartesian_join' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'rows_produced', rows_produced, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE cartesian_join = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'cartesian_join', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(cartesian_join) = 0
    UNION ALL
    SELECT
        user_name,
        'unlimited_order_by' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE unlimited_order_by = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'unlimited_order_by', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(unlimited_order_by) = 0
    UNION ALL
    SELECT
        user_name,
        'large_group_by' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'rows_produced', rows_produced, 'start_time', start_time)) WITHIN GROUP (ORDER BY rows_produced DESC) AS sample_queries
    FROM query_flags WHERE large_group_by = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'large_group_by', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(large_group_by) = 0
    UNION ALL
    SELECT
        user_name,
        'expensive_distinct' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE expensive_distinct = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'expensive_distinct', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(expensive_distinct) = 0
    UNION ALL
    SELECT
        user_name,
        'inefficient_like' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE inefficient_like = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'inefficient_like', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(inefficient_like) = 0
    UNION ALL
    SELECT
        user_name,
        'no_results_with_scan' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE no_results_with_scan = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'no_results_with_scan', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(no_results_with_scan) = 0
    UNION ALL
    SELECT
        user_name,
        'high_compile_ratio' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'compilation_time', compilation_time, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY compilation_time DESC) AS sample_queries
    FROM query_flags WHERE high_compile_ratio = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'high_compile_ratio', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(high_compile_ratio) = 0
),
pivoted_samples AS (
    SELECT
        user_name,
        OBJECT_AGG(flag_type, sample_queries) AS query_samples
    FROM sample_queries
    GROUP BY user_name
),
user_metrics AS (
    SELECT
        user_name,
        COUNT(DISTINCT query_id) AS total_queries,
        COUNT(DISTINCT warehouse_name) AS warehouses_used,
        COUNT(DISTINCT database_name) AS databases_accessed,
        ROUND(SUM(credits_used_cloud_services), 2) AS total_credits,
        ROUND(AVG(execution_time_ms), 2) AS avg_execution_time_ms,
        ROUND(AVG(bytes_scanned / NULLIF(rows_produced, 0)), 2) AS avg_bytes_per_row,
        ROUND(SUM(bytes_scanned) / POWER(1024, 3), 2) AS total_data_scanned_gb,
        ROUND(SUM(failed_cancelled) * 1.0 / NULLIF(COUNT(*), 0) * 100, 2) AS failure_cancellation_rate_pct,
        SUM(spilled) AS spilled_queries,
        SUM(over_provisioned) AS over_provisioned_queries,
        SUM(peak_hour_long_running) AS peak_hour_long_running_queries,
        SUM(select_star) AS select_star_queries,
        SUM(unpartitioned_scan) AS unpartitioned_scan_queries,
        COUNT(*) - COUNT(DISTINCT query_hash) AS repeated_queries,
        SUM(complex_query) AS complex_join_queries,
        SUM(zero_result_query) AS zero_result_queries,
        SUM(high_compile_time) AS high_compile_queries,
        SUM(untagged_query) AS untagged_queries,
        SUM(unlimited_order_by) AS unlimited_order_by_queries,
        SUM(large_group_by) AS large_group_by_queries,
        SUM(slow_query) AS slow_queries,
        SUM(expensive_distinct) AS expensive_distinct_queries,
        SUM(inefficient_like) AS inefficient_like_queries,
        SUM(no_results_with_scan) AS no_results_with_scan_queries,
        SUM(cartesian_join) AS cartesian_join_queries,
        SUM(high_compile_ratio) AS high_compile_ratio_queries,
        ROUND(
            SUM(spilled) * 5 +
            SUM(over_provisioned) * 4 +
            SUM(peak_hour_long_running) * 4 +
            SUM(select_star) * 3 +
            SUM(unpartitioned_scan) * 4 +
            (COUNT(*) - COUNT(DISTINCT query_hash)) * 2 +
            SUM(complex_query) * 3 +
            SUM(failed_cancelled) * 3 +
            SUM(zero_result_query) * 5 +
            SUM(high_compile_time) * 3 +
            SUM(untagged_query) * 2 +
            SUM(unlimited_order_by) * 3 +
            SUM(large_group_by) * 4 +
            SUM(slow_query) * 4 +
            SUM(expensive_distinct) * 4 +
            SUM(inefficient_like) * 2 +
            SUM(no_results_with_scan) * 5 +
            SUM(cartesian_join) * 6 +
            SUM(high_compile_ratio) * 3, 2
        ) AS weighted_score
    FROM query_flags
    GROUP BY user_name
),
cost_percentiles AS (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_credits) AS cost_p90 FROM user_metrics
),
bytes_percentiles AS (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY avg_bytes_per_row) AS bytes_p90 FROM user_metrics
),
failure_percentiles AS (
    SELECT PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY failure_cancellation_rate_pct) AS failure_p80 FROM user_metrics
)
SELECT
    um.user_name,
    um.total_queries,
    um.warehouses_used,
    um.databases_accessed,
    um.total_credits,
    um.avg_execution_time_ms,
    um.avg_bytes_per_row,
    um.total_data_scanned_gb,
    um.failure_cancellation_rate_pct,
    um.spilled_queries,
    um.over_provisioned_queries,
    um.peak_hour_long_running_queries,
    um.select_star_queries,
    um.unpartitioned_scan_queries,
    um.repeated_queries,
    um.complex_join_queries,
    um.zero_result_queries,
    um.high_compile_queries,
    um.untagged_queries,
    um.unlimited_order_by_queries,
    um.large_group_by_queries,
    um.slow_queries,
    um.expensive_distinct_queries,
    um.inefficient_like_queries,
    um.no_results_with_scan_queries,
    um.cartesian_join_queries,
    um.high_compile_ratio_queries,
    um.weighted_score,
    CASE WHEN um.total_credits > cost_percentiles.cost_p90 THEN 'High Cost' ELSE 'Normal' END AS cost_status,
    ARRAY_CONSTRUCT_COMPACT(
        CASE WHEN um.avg_bytes_per_row > bytes_percentiles.bytes_p90 THEN 'Reduce data scanned with clustering or selective columns.' ELSE NULL END,
        CASE WHEN um.failure_cancellation_rate_pct > failure_percentiles.failure_p80 THEN 'Debug query syntax and logic.' ELSE NULL END,
        CASE WHEN um.spilled_queries > 10 THEN 'Optimize memory usage or increase warehouse size.' ELSE NULL END,
        CASE WHEN um.over_provisioned_queries > 10 THEN 'Use smaller warehouses for simple queries.' ELSE NULL END,
        CASE WHEN um.peak_hour_long_running_queries > 10 THEN 'Schedule long queries off-peak.' ELSE NULL END,
        CASE WHEN um.select_star_queries > 10 THEN 'Specify columns instead of SELECT *.' ELSE NULL END,
        CASE WHEN um.unpartitioned_scan_queries > 10 THEN 'Implement partitioning or clustering.' ELSE NULL END,
        CASE WHEN um.repeated_queries > 10 THEN 'Review frequently executed queries.' ELSE NULL END,
        CASE WHEN um.complex_join_queries > 10 THEN 'Simplify complex JOINs or window functions.' ELSE NULL END,
        CASE WHEN um.zero_result_queries > 10 THEN 'Avoid queries that return no data.' ELSE NULL END,
        CASE WHEN um.high_compile_queries > 10 THEN 'Refactor complex SQL to reduce compile time.' ELSE NULL END,
        CASE WHEN um.untagged_queries > 10 THEN 'Apply query tags for cost attribution.' ELSE NULL END,
        CASE WHEN um.unlimited_order_by_queries > 5 THEN 'Add LIMIT clauses to ORDER BY queries.' ELSE NULL END,
        CASE WHEN um.large_group_by_queries > 5 THEN 'Optimize GROUP BY operations with pre-aggregation.' ELSE NULL END,
        CASE WHEN um.slow_queries > 5 THEN 'Optimize slow-running queries.' ELSE NULL END,
        CASE WHEN um.expensive_distinct_queries > 5 THEN 'Replace DISTINCT with GROUP BY where possible.' ELSE NULL END,
        CASE WHEN um.inefficient_like_queries > 10 THEN 'Use proper indexing for LIKE operations.' ELSE NULL END,
        CASE WHEN um.no_results_with_scan_queries > 5 THEN 'Add WHERE clauses to prevent unnecessary scans.' ELSE NULL END,
        CASE WHEN um.cartesian_join_queries > 0 THEN 'Review JOIN conditions to avoid Cartesian products.' ELSE NULL END,
        CASE WHEN um.high_compile_ratio_queries > 5 THEN 'Simplify query complexity to reduce compilation overhead.' ELSE NULL END
    ) AS recommendations,
    COALESCE(ps.query_samples, OBJECT_CONSTRUCT()) AS query_samples
FROM user_metrics um
CROSS JOIN cost_percentiles
CROSS JOIN bytes_percentiles
CROSS JOIN failure_percentiles
LEFT JOIN pivoted_samples ps ON um.user_name = ps.user_name
ORDER BY weighted_score DESC;

pormpt

Armaan Khan — Thu, 07 Aug 2025 06:56:30 +0000

i will share my queries and requrometns . you have to create a react and falsk as backed and we haev to run qurires to snowflakes andthan we haev to passed the data to react and than we have to create a interactive table based on that .this is my warehouse query CREATE OR REPLACE TABLE WAREHOUSE_ANALYTICS_DASHBOARD_with_queries AS

WITH warehouse_info AS (

    -- Get warehouse metadata

    SELECT DISTINCT

        wh.WAREHOUSE_ID,

        wh.WAREHOUSE_NAME,

        wh.SIZE,

        wh.WAREHOUSE_TYPE,

        wh.CLUSTER_COUNT,

        NULL as SUSPEND_POLICY,

        NULL as MIN_CLUSTER_COUNT,

        NULL as MAX_CLUSTER_COUNT

    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY wh

    WHERE wh.TIMESTAMP >= CURRENT_DATE - 1

),



query_buckets AS (

    SELECT

        q.WAREHOUSE_ID,

        q.WAREHOUSE_NAME,

        q.QUERY_ID,

        q.TOTAL_ELAPSED_TIME,

        q.EXECUTION_STATUS,

        q.CREDITS_USED_CLOUD_SERVICES,

        q.BYTES_SPILLED_TO_LOCAL_STORAGE,

        q.BYTES_SPILLED_TO_REMOTE_STORAGE,

        q.QUERY_TYPE,



        CASE

            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'

            WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'

            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'

            WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'

            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'

            ELSE '5+ minutes'

        END as DURATION_BUCKET,



        CASE

            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 120000 THEN '1-2 minutes'

            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 300000 THEN '2-5 minutes'

            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 600000 THEN '5-10 minutes'

            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 1200000 THEN '10-20 minutes'

            ELSE '20+ minutes'

        END as QUEUED_BUCKET,



        CASE

            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'

            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'

            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'

            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'

            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'

            ELSE '100+ cents'

        END as CREDIT_UTILIZATION_BUCKET



    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q

    WHERE q.START_TIME >= CURRENT_DATE - 1

        AND q.WAREHOUSE_ID IS NOT NULL

        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')

        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')

        AND q.QUERY_TEXT IS NOT NULL

),



warehouse_metrics AS (

    SELECT

        wm.WAREHOUSE_ID,

        wm.WAREHOUSE_NAME,

        SUM(wm.CREDITS_USED) as TOTAL_CREDITS_USED,

        SUM(wm.CREDITS_USED_COMPUTE) as TOTAL_COMPUTE_CREDITS,

        SUM(wm.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CLOUD_SERVICES_CREDITS

    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm

    WHERE wm.START_TIME >= CURRENT_DATE - 1

    GROUP BY wm.WAREHOUSE_ID, wm.WAREHOUSE_NAME

)



SELECT

    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID) as WAREHOUSE_ID,

    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME) as WAREHOUSE_NAME,

    wi.SIZE as WAREHOUSE_SIZE,

    wi.WAREHOUSE_TYPE,

    wi.CLUSTER_COUNT,

    wi.SUSPEND_POLICY,

    wi.MIN_CLUSTER_COUNT,

    wi.MAX_CLUSTER_COUNT,



    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN 1 END) as QUERIES_1_10_SEC,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN 1 END) as QUERIES_10_20_SEC,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN 1 END) as QUERIES_20_60_SEC,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN 1 END) as QUERIES_1_3_MIN,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN 1 END) as QUERIES_3_5_MIN,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN 1 END) as QUERIES_5_PLUS_MIN,



    COUNT(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN 1 END) as QUEUED_1_2_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN 1 END) as QUEUED_2_5_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN 1 END) as QUEUED_5_10_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN 1 END) as QUEUED_10_20_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN 1 END) as QUEUED_20_PLUS_MIN,



    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_LOCAL,

    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_REMOTE,

    SUM(qb.BYTES_SPILLED_TO_LOCAL_STORAGE) as TOTAL_BYTES_SPILLED_LOCAL,

    SUM(qb.BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_REMOTE,



    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,

    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,

    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,



    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN 1 END) as QUERIES_0_20_CENTS,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN 1 END) as QUERIES_20_40_CENTS,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN 1 END) as QUERIES_40_60_CENTS,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN 1 END) as QUERIES_60_80_CENTS,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN 1 END) as QUERIES_80_100_CENTS,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN 1 END) as QUERIES_100_PLUS_CENTS,



    -- New column: query_ids

    OBJECT_CONSTRUCT(

        '1-10_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN qb.QUERY_ID END),

        '10-20_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN qb.QUERY_ID END),

        '20-60_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN qb.QUERY_ID END),

        '1-3_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN qb.QUERY_ID END),

        '3-5_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN qb.QUERY_ID END),

        '5_plus_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN qb.QUERY_ID END),

        'queued_1-2_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN qb.QUERY_ID END),

        'queued_2-5_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN qb.QUERY_ID END),

        'queued_5-10_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN qb.QUERY_ID END),

        'queued_10-20_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN qb.QUERY_ID END),

        'queued_20_plus_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN qb.QUERY_ID END),

        'spilled_local_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN qb.QUERY_ID END),

        'spilled_remote_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN qb.QUERY_ID END),

        'failed_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN qb.QUERY_ID END),

        'successful_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN qb.QUERY_ID END),

        'running_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN qb.QUERY_ID END),

        'credit_0-20_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN qb.QUERY_ID END),

        'credit_20-40_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN qb.QUERY_ID END),

        'credit_40-60_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN qb.QUERY_ID END),

        'credit_60-80_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN qb.QUERY_ID END),

        'credit_80-100_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN qb.QUERY_ID END),

        'credit_100_plus_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN qb.QUERY_ID END)

    ) as QUERY_IDS,



    COUNT(qb.QUERY_ID) as TOTAL_QUERIES,

    COALESCE(wm.TOTAL_CREDITS_USED, 0) as TOTAL_CREDITS_USED,

    COALESCE(wm.TOTAL_COMPUTE_CREDITS, 0) as TOTAL_COMPUTE_CREDITS,

    COALESCE(wm.TOTAL_CLOUD_SERVICES_CREDITS, 0) as TOTAL_CLOUD_SERVICES_CREDITS,



    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,

    CURRENT_DATE - 1 as ANALYSIS_DATE



FROM warehouse_info wi

FULL OUTER JOIN query_buckets qb

    ON wi.WAREHOUSE_ID = qb.WAREHOUSE_ID

FULL OUTER JOIN warehouse_metrics wm

    ON COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID) = wm.WAREHOUSE_ID



GROUP BY

    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID),

    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME),

    wi.SIZE,

    wi.WAREHOUSE_TYPE,

    wi.CLUSTER_COUNT,

    wi.SUSPEND_POLICY,

    wi.MIN_CLUSTER_COUNT,

    wi.MAX_CLUSTER_COUNT,

    wm.TOTAL_CREDITS_USED,

    wm.TOTAL_COMPUTE_CREDITS,

    wm.TOTAL_CLOUD_SERVICES_CREDITS



ORDER BY TOTAL_QUERIES DESC;

and this is the qures query CREATE OR REPLACE TABLE QUERY_HISTORY_SUMMARY AS

SELECT 

    -- Query Identification

    q.QUERY_ID,

    q.QUERY_HASH,

    q.QUERY_PARAMETERIZED_HASH,

    LEFT(q.QUERY_TEXT, 100) as QUERY_TEXT_PREVIEW,

    q.QUERY_TYPE,

    q.QUERY_TAG,



    -- Timing Information

    q.START_TIME,

    q.END_TIME,

    q.TOTAL_ELAPSED_TIME,

    q.COMPILATION_TIME,

    q.EXECUTION_TIME,



    -- User and Session Info

    q.USER_NAME,

    q.USER_TYPE,

    q.ROLE_NAME,

    q.ROLE_TYPE,

    q.SESSION_ID,



    -- Warehouse Information

    q.WAREHOUSE_ID,

    q.WAREHOUSE_NAME,

    q.WAREHOUSE_SIZE,

    q.WAREHOUSE_TYPE,

    q.CLUSTER_NUMBER,



    -- Database Context

    q.DATABASE_ID,

    q.DATABASE_NAME,

    q.SCHEMA_ID,

    q.SCHEMA_NAME,

    q.USER_DATABASE_NAME,

    q.USER_SCHEMA_NAME,



    -- Execution Status

    q.EXECUTION_STATUS,

    q.ERROR_CODE,

    LEFT(q.ERROR_MESSAGE, 200) as ERROR_MESSAGE_PREVIEW,



    -- Performance Metrics

    q.BYTES_SCANNED,

    q.PERCENTAGE_SCANNED_FROM_CACHE,

    q.BYTES_WRITTEN,

    q.ROWS_PRODUCED,

    q.ROWS_INSERTED,

    q.ROWS_UPDATED,

    q.ROWS_DELETED,



    -- Resource Usage

    q.CREDITS_USED_CLOUD_SERVICES,

    q.BYTES_SPILLED_TO_LOCAL_STORAGE,

    q.BYTES_SPILLED_TO_REMOTE_STORAGE,

    q.PARTITIONS_SCANNED,

    q.PARTITIONS_TOTAL,



    -- Queue Times

    q.QUEUED_PROVISIONING_TIME,

    q.QUEUED_REPAIR_TIME,

    q.QUEUED_OVERLOAD_TIME,

    q.TRANSACTION_BLOCKED_TIME,



    -- Classification Buckets for easy filtering

    CASE 

        WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'

        WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds' 

        WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'

        WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'

        WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'

        ELSE '5+ minutes'

    END as DURATION_BUCKET,



    CASE 

        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'

        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'

        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'

        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'

        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'

        ELSE '100+ cents'

    END as CREDIT_BUCKET,



    CASE 

        WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'SPILLED'

        ELSE 'NO_SPILL'

    END as SPILL_STATUS,



    CASE 

        WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) > 0 THEN 'QUEUED'

        ELSE 'NOT_QUEUED'

    END as QUEUE_STATUS,



    -- Analysis metadata

    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,

    CURRENT_DATE - 1 as ANALYSIS_DATE



FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q

WHERE q.START_TIME >= CURRENT_DATE - 1

    AND q.QUERY_ID IS NOT NULL

ORDER BY q.START_TIME DESC;



-- =====================================================

-- TABLE 2: QUERY_DETAILS_COMPLETE

-- This table provides complete details for any specific query

-- =====================================================



CREATE OR REPLACE TABLE QUERY_DETAILS_COMPLETE AS

WITH filtered_queries AS (

    SELECT

        q.*,

        -- Calculate partition scan percentage

        CASE

            WHEN q.PARTITIONS_TOTAL > 0 THEN

                ROUND((q.PARTITIONS_SCANNED::FLOAT / q.PARTITIONS_TOTAL::FLOAT) * 100, 2)

            ELSE 0

        END as PARTITION_SCAN_PERCENTAGE,

        

        -- Calculate compilation time percentage

        CASE

            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN

                ROUND((q.COMPILATION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)

            ELSE 0

        END as COMPILATION_TIME_PERCENTAGE,

        

        -- Calculate execution time percentage

        CASE

            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN

                ROUND((q.EXECUTION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)

            ELSE 0

        END as EXECUTION_TIME_PERCENTAGE,

        

        -- Calculate rows per MB scanned

        CASE

            WHEN q.BYTES_SCANNED > 0 THEN

                ROUND(q.ROWS_PRODUCED::FLOAT / (q.BYTES_SCANNED::FLOAT / 1024 / 1024), 2)

            ELSE 0

        END as ROWS_PER_MB_SCANNED,

        

        -- Classify performance

        CASE

            WHEN q.TOTAL_ELAPSED_TIME <= 1000 THEN 'VERY_FAST'

            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN 'FAST'

            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN 'MODERATE'

            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN 'SLOW'

            ELSE 'VERY_SLOW'

        END as PERFORMANCE_CATEGORY,

        

        -- Classify cache efficiency

        CASE

            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 90 THEN 'HIGH_CACHE_HIT'

            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 50 THEN 'MEDIUM_CACHE_HIT'

            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE > 0 THEN 'LOW_CACHE_HIT'

            ELSE 'NO_CACHE_HIT'

        END as CACHE_EFFICIENCY,

        

        -- Classify spilling

        CASE

            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 AND q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'BOTH_SPILL'

            WHEN q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'REMOTE_SPILL'

            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 'LOCAL_SPILL'

            ELSE 'NO_SPILL'

        END as SPILL_CLASSIFICATION

        

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q

    WHERE q.START_TIME >= CURRENT_DATE - 1

        AND q.QUERY_ID IS NOT NULL

        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')

        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')

)

SELECT 

    -- Core Query Information

    QUERY_ID,

    QUERY_TEXT,

    QUERY_HASH,

    QUERY_HASH_VERSION,

    QUERY_PARAMETERIZED_HASH,

    QUERY_PARAMETERIZED_HASH_VERSION,

    QUERY_TYPE,

    QUERY_TAG,



    -- Timing Details (all in milliseconds)

    START_TIME,

    END_TIME,

    TOTAL_ELAPSED_TIME,

    COMPILATION_TIME,

    EXECUTION_TIME,

    QUEUED_PROVISIONING_TIME,

    QUEUED_REPAIR_TIME,

    QUEUED_OVERLOAD_TIME,

    TRANSACTION_BLOCKED_TIME,

    CHILD_QUERIES_WAIT_TIME,

    QUERY_RETRY_TIME,

    QUERY_RETRY_CAUSE,

    FAULT_HANDLING_TIME,

    LIST_EXTERNAL_FILES_TIME,



    -- User and Authentication

    USER_NAME,

    USER_TYPE,

    ROLE_NAME,

    ROLE_TYPE,

    SECONDARY_ROLE_STATS,

    SESSION_ID,



    -- Warehouse and Compute

    WAREHOUSE_ID,

    WAREHOUSE_NAME,

    WAREHOUSE_SIZE,

    WAREHOUSE_TYPE,

    CLUSTER_NUMBER,

    QUERY_LOAD_PERCENT,



    -- Database Context

    DATABASE_ID,

    DATABASE_NAME,

    SCHEMA_ID,

    SCHEMA_NAME,

    USER_DATABASE_ID,

    USER_DATABASE_NAME,

    USER_SCHEMA_ID,

    USER_SCHEMA_NAME,



    -- Execution Results

    EXECUTION_STATUS,

    ERROR_CODE,

    ERROR_MESSAGE,

    IS_CLIENT_GENERATED_STATEMENT,



    -- Data Processing Metrics

    BYTES_SCANNED,

    PERCENTAGE_SCANNED_FROM_CACHE,

    BYTES_WRITTEN,

    BYTES_WRITTEN_TO_RESULT,

    BYTES_READ_FROM_RESULT,

    ROWS_PRODUCED,

    ROWS_WRITTEN_TO_RESULT,

    ROWS_INSERTED,

    ROWS_UPDATED,

    ROWS_DELETED,

    ROWS_UNLOADED,

    BYTES_DELETED,



    -- Partitioning

    PARTITIONS_SCANNED,

    PARTITIONS_TOTAL,

    PARTITION_SCAN_PERCENTAGE,



    -- Memory and Spilling

    BYTES_SPILLED_TO_LOCAL_STORAGE,

    BYTES_SPILLED_TO_REMOTE_STORAGE,

    BYTES_SENT_OVER_THE_NETWORK,



    -- Credits and Cost

    CREDITS_USED_CLOUD_SERVICES,



    -- Data Transfer

    OUTBOUND_DATA_TRANSFER_CLOUD,

    OUTBOUND_DATA_TRANSFER_REGION,

    OUTBOUND_DATA_TRANSFER_BYTES,

    INBOUND_DATA_TRANSFER_CLOUD,

    INBOUND_DATA_TRANSFER_REGION,

    INBOUND_DATA_TRANSFER_BYTES,



    -- External Functions

    EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,

    EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,

    EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,

    EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,

    EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,



    -- Query Acceleration

    QUERY_ACCELERATION_BYTES_SCANNED,

    QUERY_ACCELERATION_PARTITIONS_SCANNED,

    QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,



    -- Transaction Information

    TRANSACTION_ID,

    

    -- System Information

    RELEASE_VERSION,



    -- Performance Ratios and Calculated Fields

    COMPILATION_TIME_PERCENTAGE,

    EXECUTION_TIME_PERCENTAGE,

    ROWS_PER_MB_SCANNED,



    -- Performance Classifications

    PERFORMANCE_CATEGORY,

    CACHE_EFFICIENCY,

    SPILL_CLASSIFICATION,



    -- Analysis metadata

    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,

    CURRENT_DATE - 1 as ANALYSIS_DATE



FROM filtered_queries

ORDER BY START_TIME DESC;



-- now what you have to do create a python flask api i will pass you the snwoalke curosr dicetly jutst take that and expcutre queire to get this three tabels ad stored in csv json or fatsted or best file fomrts in locak . than use that as caced data . now what i want is that in react based on this data first creat a warehouse table an all queires colmn in warehuse tabel are clickable . than wehn click on that so take to query pagge gropy by user . so take the warehouse name , and id for that sepcifl click . for exmple if click on 1-10 section quries so only take the queires id for that and tan on query hsirot or summary tabel extart those query and gripu by user and thna hsow the table . for exmple in warehouse table i click on 1-10 section quires which is 35 so wehn i click on that so you have to show all those 35 quries with respose to user . in table . than wehn i click on user so show all the query for that user on that category . and than shwo a tabel with quees and a button to show detailed quries and than when click on that shwo the fulld etaled queres .

armaan

Armaan Khan — Thu, 07 Aug 2025 05:34:55 +0000

CREATE OR REPLACE TABLE WAREHOUSE_ANALYTICS_DASHBOARD_with_queries AS
WITH warehouse_info AS (
    -- Get warehouse metadata
    SELECT DISTINCT
        wh.WAREHOUSE_ID,
        wh.WAREHOUSE_NAME,
        wh.SIZE,
        wh.WAREHOUSE_TYPE,
        wh.CLUSTER_COUNT,
        NULL as SUSPEND_POLICY,
        NULL as MIN_CLUSTER_COUNT,
        NULL as MAX_CLUSTER_COUNT
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_EVENTS_HISTORY wh
    WHERE wh.TIMESTAMP >= CURRENT_DATE - 1
),

query_buckets AS (
    SELECT
        q.WAREHOUSE_ID,
        q.WAREHOUSE_NAME,
        q.QUERY_ID,
        q.TOTAL_ELAPSED_TIME,
        q.EXECUTION_STATUS,
        q.CREDITS_USED_CLOUD_SERVICES,
        q.BYTES_SPILLED_TO_LOCAL_STORAGE,
        q.BYTES_SPILLED_TO_REMOTE_STORAGE,
        q.QUERY_TYPE,

        CASE
            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
            WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
            ELSE '5+ minutes'
        END as DURATION_BUCKET,

        CASE
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 120000 THEN '1-2 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 300000 THEN '2-5 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 600000 THEN '5-10 minutes'
            WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) <= 1200000 THEN '10-20 minutes'
            ELSE '20+ minutes'
        END as QUEUED_BUCKET,

        CASE
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
            WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
            ELSE '100+ cents'
        END as CREDIT_UTILIZATION_BUCKET

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
    WHERE q.START_TIME >= CURRENT_DATE - 1
        AND q.WAREHOUSE_ID IS NOT NULL
        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')
        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')
        AND q.QUERY_TEXT IS NOT NULL
),

warehouse_metrics AS (
    SELECT
        wm.WAREHOUSE_ID,
        wm.WAREHOUSE_NAME,
        SUM(wm.CREDITS_USED) as TOTAL_CREDITS_USED,
        SUM(wm.CREDITS_USED_COMPUTE) as TOTAL_COMPUTE_CREDITS,
        SUM(wm.CREDITS_USED_CLOUD_SERVICES) as TOTAL_CLOUD_SERVICES_CREDITS
    FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY wm
    WHERE wm.START_TIME >= CURRENT_DATE - 1
    GROUP BY wm.WAREHOUSE_ID, wm.WAREHOUSE_NAME
)

SELECT
    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID) as WAREHOUSE_ID,
    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME) as WAREHOUSE_NAME,
    wi.SIZE as WAREHOUSE_SIZE,
    wi.WAREHOUSE_TYPE,
    wi.CLUSTER_COUNT,
    wi.SUSPEND_POLICY,
    wi.MIN_CLUSTER_COUNT,
    wi.MAX_CLUSTER_COUNT,

    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN 1 END) as QUERIES_1_10_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN 1 END) as QUERIES_10_20_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN 1 END) as QUERIES_20_60_SEC,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN 1 END) as QUERIES_1_3_MIN,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN 1 END) as QUERIES_3_5_MIN,
    COUNT(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN 1 END) as QUERIES_5_PLUS_MIN,

    COUNT(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN 1 END) as QUEUED_1_2_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN 1 END) as QUEUED_2_5_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN 1 END) as QUEUED_5_10_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN 1 END) as QUEUED_10_20_MIN,
    COUNT(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN 1 END) as QUEUED_20_PLUS_MIN,

    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_LOCAL,
    COUNT(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 1 END) as QUERIES_SPILLED_REMOTE,
    SUM(qb.BYTES_SPILLED_TO_LOCAL_STORAGE) as TOTAL_BYTES_SPILLED_LOCAL,
    SUM(qb.BYTES_SPILLED_TO_REMOTE_STORAGE) as TOTAL_BYTES_SPILLED_REMOTE,

    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN 1 END) as FAILED_QUERIES,
    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN 1 END) as SUCCESSFUL_QUERIES,
    COUNT(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN 1 END) as RUNNING_QUERIES,

    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN 1 END) as QUERIES_0_20_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN 1 END) as QUERIES_20_40_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN 1 END) as QUERIES_40_60_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN 1 END) as QUERIES_60_80_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN 1 END) as QUERIES_80_100_CENTS,
    COUNT(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN 1 END) as QUERIES_100_PLUS_CENTS,

    -- New column: query_ids
    OBJECT_CONSTRUCT(
        '1-10_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-10 seconds' THEN qb.QUERY_ID END),
        '10-20_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '10-20 seconds' THEN qb.QUERY_ID END),
        '20-60_sec_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '20-60 seconds' THEN qb.QUERY_ID END),
        '1-3_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '1-3 minutes' THEN qb.QUERY_ID END),
        '3-5_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '3-5 minutes' THEN qb.QUERY_ID END),
        '5_plus_min_ids', ARRAY_AGG(CASE WHEN qb.DURATION_BUCKET = '5+ minutes' THEN qb.QUERY_ID END),
        'queued_1-2_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '1-2 minutes' THEN qb.QUERY_ID END),
        'queued_2-5_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '2-5 minutes' THEN qb.QUERY_ID END),
        'queued_5-10_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '5-10 minutes' THEN qb.QUERY_ID END),
        'queued_10-20_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '10-20 minutes' THEN qb.QUERY_ID END),
        'queued_20_plus_min_ids', ARRAY_AGG(CASE WHEN qb.QUEUED_BUCKET = '20+ minutes' THEN qb.QUERY_ID END),
        'spilled_local_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN qb.QUERY_ID END),
        'spilled_remote_ids', ARRAY_AGG(CASE WHEN qb.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN qb.QUERY_ID END),
        'failed_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'FAIL' THEN qb.QUERY_ID END),
        'successful_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'SUCCESS' THEN qb.QUERY_ID END),
        'running_queries_ids', ARRAY_AGG(CASE WHEN qb.EXECUTION_STATUS = 'RUNNING' THEN qb.QUERY_ID END),
        'credit_0-20_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '0-20 cents' THEN qb.QUERY_ID END),
        'credit_20-40_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '20-40 cents' THEN qb.QUERY_ID END),
        'credit_40-60_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '40-60 cents' THEN qb.QUERY_ID END),
        'credit_60-80_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '60-80 cents' THEN qb.QUERY_ID END),
        'credit_80-100_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '80-100 cents' THEN qb.QUERY_ID END),
        'credit_100_plus_cents_ids', ARRAY_AGG(CASE WHEN qb.CREDIT_UTILIZATION_BUCKET = '100+ cents' THEN qb.QUERY_ID END)
    ) as QUERY_IDS,

    COUNT(qb.QUERY_ID) as TOTAL_QUERIES,
    COALESCE(wm.TOTAL_CREDITS_USED, 0) as TOTAL_CREDITS_USED,
    COALESCE(wm.TOTAL_COMPUTE_CREDITS, 0) as TOTAL_COMPUTE_CREDITS,
    COALESCE(wm.TOTAL_CLOUD_SERVICES_CREDITS, 0) as TOTAL_CLOUD_SERVICES_CREDITS,

    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM warehouse_info wi
FULL OUTER JOIN query_buckets qb
    ON wi.WAREHOUSE_ID = qb.WAREHOUSE_ID
FULL OUTER JOIN warehouse_metrics wm
    ON COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID) = wm.WAREHOUSE_ID

GROUP BY
    COALESCE(wi.WAREHOUSE_ID, qb.WAREHOUSE_ID, wm.WAREHOUSE_ID),
    COALESCE(wi.WAREHOUSE_NAME, qb.WAREHOUSE_NAME, wm.WAREHOUSE_NAME),
    wi.SIZE,
    wi.WAREHOUSE_TYPE,
    wi.CLUSTER_COUNT,
    wi.SUSPEND_POLICY,
    wi.MIN_CLUSTER_COUNT,
    wi.MAX_CLUSTER_COUNT,
    wm.TOTAL_CREDITS_USED,
    wm.TOTAL_COMPUTE_CREDITS,
    wm.TOTAL_CLOUD_SERVICES_CREDITS

ORDER BY TOTAL_QUERIES DESC;


CREATE OR REPLACE TABLE QUERY_HISTORY_SUMMARY AS
SELECT 
    -- Query Identification
    q.QUERY_ID,
    q.QUERY_HASH,
    q.QUERY_PARAMETERIZED_HASH,
    LEFT(q.QUERY_TEXT, 100) as QUERY_TEXT_PREVIEW,
    q.QUERY_TYPE,
    q.QUERY_TAG,

    -- Timing Information
    q.START_TIME,
    q.END_TIME,
    q.TOTAL_ELAPSED_TIME,
    q.COMPILATION_TIME,
    q.EXECUTION_TIME,

    -- User and Session Info
    q.USER_NAME,
    q.USER_TYPE,
    q.ROLE_NAME,
    q.ROLE_TYPE,
    q.SESSION_ID,

    -- Warehouse Information
    q.WAREHOUSE_ID,
    q.WAREHOUSE_NAME,
    q.WAREHOUSE_SIZE,
    q.WAREHOUSE_TYPE,
    q.CLUSTER_NUMBER,

    -- Database Context
    q.DATABASE_ID,
    q.DATABASE_NAME,
    q.SCHEMA_ID,
    q.SCHEMA_NAME,
    q.USER_DATABASE_NAME,
    q.USER_SCHEMA_NAME,

    -- Execution Status
    q.EXECUTION_STATUS,
    q.ERROR_CODE,
    LEFT(q.ERROR_MESSAGE, 200) as ERROR_MESSAGE_PREVIEW,

    -- Performance Metrics
    q.BYTES_SCANNED,
    q.PERCENTAGE_SCANNED_FROM_CACHE,
    q.BYTES_WRITTEN,
    q.ROWS_PRODUCED,
    q.ROWS_INSERTED,
    q.ROWS_UPDATED,
    q.ROWS_DELETED,

    -- Resource Usage
    q.CREDITS_USED_CLOUD_SERVICES,
    q.BYTES_SPILLED_TO_LOCAL_STORAGE,
    q.BYTES_SPILLED_TO_REMOTE_STORAGE,
    q.PARTITIONS_SCANNED,
    q.PARTITIONS_TOTAL,

    -- Queue Times
    q.QUEUED_PROVISIONING_TIME,
    q.QUEUED_REPAIR_TIME,
    q.QUEUED_OVERLOAD_TIME,
    q.TRANSACTION_BLOCKED_TIME,

    -- Classification Buckets for easy filtering
    CASE 
        WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN '1-10 seconds'
        WHEN q.TOTAL_ELAPSED_TIME <= 20000 THEN '10-20 seconds' 
        WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN '20-60 seconds'
        WHEN q.TOTAL_ELAPSED_TIME <= 180000 THEN '1-3 minutes'
        WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN '3-5 minutes'
        ELSE '5+ minutes'
    END as DURATION_BUCKET,

    CASE 
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.2 THEN '0-20 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.4 THEN '20-40 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.6 THEN '40-60 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 0.8 THEN '60-80 cents'
        WHEN q.CREDITS_USED_CLOUD_SERVICES <= 1.0 THEN '80-100 cents'
        ELSE '100+ cents'
    END as CREDIT_BUCKET,

    CASE 
        WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 OR q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'SPILLED'
        ELSE 'NO_SPILL'
    END as SPILL_STATUS,

    CASE 
        WHEN (q.QUEUED_PROVISIONING_TIME + q.QUEUED_REPAIR_TIME + q.QUEUED_OVERLOAD_TIME) > 0 THEN 'QUEUED'
        ELSE 'NOT_QUEUED'
    END as QUEUE_STATUS,

    -- Analysis metadata
    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
WHERE q.START_TIME >= CURRENT_DATE - 1
    AND q.QUERY_ID IS NOT NULL
ORDER BY q.START_TIME DESC;

-- =====================================================
-- TABLE 2: QUERY_DETAILS_COMPLETE
-- This table provides complete details for any specific query
-- =====================================================

CREATE OR REPLACE TABLE QUERY_DETAILS_COMPLETE AS
WITH filtered_queries AS (
    SELECT
        q.*,
        -- Calculate partition scan percentage
        CASE
            WHEN q.PARTITIONS_TOTAL > 0 THEN
                ROUND((q.PARTITIONS_SCANNED::FLOAT / q.PARTITIONS_TOTAL::FLOAT) * 100, 2)
            ELSE 0
        END as PARTITION_SCAN_PERCENTAGE,

        -- Calculate compilation time percentage
        CASE
            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
                ROUND((q.COMPILATION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
            ELSE 0
        END as COMPILATION_TIME_PERCENTAGE,

        -- Calculate execution time percentage
        CASE
            WHEN q.TOTAL_ELAPSED_TIME > 0 THEN
                ROUND((q.EXECUTION_TIME::FLOAT / q.TOTAL_ELAPSED_TIME::FLOAT) * 100, 2)
            ELSE 0
        END as EXECUTION_TIME_PERCENTAGE,

        -- Calculate rows per MB scanned
        CASE
            WHEN q.BYTES_SCANNED > 0 THEN
                ROUND(q.ROWS_PRODUCED::FLOAT / (q.BYTES_SCANNED::FLOAT / 1024 / 1024), 2)
            ELSE 0
        END as ROWS_PER_MB_SCANNED,

        -- Classify performance
        CASE
            WHEN q.TOTAL_ELAPSED_TIME <= 1000 THEN 'VERY_FAST'
            WHEN q.TOTAL_ELAPSED_TIME <= 10000 THEN 'FAST'
            WHEN q.TOTAL_ELAPSED_TIME <= 60000 THEN 'MODERATE'
            WHEN q.TOTAL_ELAPSED_TIME <= 300000 THEN 'SLOW'
            ELSE 'VERY_SLOW'
        END as PERFORMANCE_CATEGORY,

        -- Classify cache efficiency
        CASE
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 90 THEN 'HIGH_CACHE_HIT'
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE >= 50 THEN 'MEDIUM_CACHE_HIT'
            WHEN q.PERCENTAGE_SCANNED_FROM_CACHE > 0 THEN 'LOW_CACHE_HIT'
            ELSE 'NO_CACHE_HIT'
        END as CACHE_EFFICIENCY,

        -- Classify spilling
        CASE
            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 AND q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'BOTH_SPILL'
            WHEN q.BYTES_SPILLED_TO_REMOTE_STORAGE > 0 THEN 'REMOTE_SPILL'
            WHEN q.BYTES_SPILLED_TO_LOCAL_STORAGE > 0 THEN 'LOCAL_SPILL'
            ELSE 'NO_SPILL'
        END as SPILL_CLASSIFICATION

    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
    WHERE q.START_TIME >= CURRENT_DATE - 1
        AND q.QUERY_ID IS NOT NULL
        AND q.USER_NAME NOT IN ('SNOWFLAKE', 'SNOWFLAKE_MONITOR')
        AND q.QUERY_TYPE NOT IN ('SHOW', 'DESCRIBE', 'USE', 'CREATE', 'DROP', 'ALTER')
)
SELECT 
    -- Core Query Information
    QUERY_ID,
    QUERY_TEXT,
    QUERY_HASH,
    QUERY_HASH_VERSION,
    QUERY_PARAMETERIZED_HASH,
    QUERY_PARAMETERIZED_HASH_VERSION,
    QUERY_TYPE,
    QUERY_TAG,

    -- Timing Details (all in milliseconds)
    START_TIME,
    END_TIME,
    TOTAL_ELAPSED_TIME,
    COMPILATION_TIME,
    EXECUTION_TIME,
    QUEUED_PROVISIONING_TIME,
    QUEUED_REPAIR_TIME,
    QUEUED_OVERLOAD_TIME,
    TRANSACTION_BLOCKED_TIME,
    CHILD_QUERIES_WAIT_TIME,
    QUERY_RETRY_TIME,
    QUERY_RETRY_CAUSE,
    FAULT_HANDLING_TIME,
    LIST_EXTERNAL_FILES_TIME,

    -- User and Authentication
    USER_NAME,
    USER_TYPE,
    ROLE_NAME,
    ROLE_TYPE,
    SECONDARY_ROLE_STATS,
    SESSION_ID,

    -- Warehouse and Compute
    WAREHOUSE_ID,
    WAREHOUSE_NAME,
    WAREHOUSE_SIZE,
    WAREHOUSE_TYPE,
    CLUSTER_NUMBER,
    QUERY_LOAD_PERCENT,

    -- Database Context
    DATABASE_ID,
    DATABASE_NAME,
    SCHEMA_ID,
    SCHEMA_NAME,
    USER_DATABASE_ID,
    USER_DATABASE_NAME,
    USER_SCHEMA_ID,
    USER_SCHEMA_NAME,

    -- Execution Results
    EXECUTION_STATUS,
    ERROR_CODE,
    ERROR_MESSAGE,
    IS_CLIENT_GENERATED_STATEMENT,

    -- Data Processing Metrics
    BYTES_SCANNED,
    PERCENTAGE_SCANNED_FROM_CACHE,
    BYTES_WRITTEN,
    BYTES_WRITTEN_TO_RESULT,
    BYTES_READ_FROM_RESULT,
    ROWS_PRODUCED,
    ROWS_WRITTEN_TO_RESULT,
    ROWS_INSERTED,
    ROWS_UPDATED,
    ROWS_DELETED,
    ROWS_UNLOADED,
    BYTES_DELETED,

    -- Partitioning
    PARTITIONS_SCANNED,
    PARTITIONS_TOTAL,
    PARTITION_SCAN_PERCENTAGE,

    -- Memory and Spilling
    BYTES_SPILLED_TO_LOCAL_STORAGE,
    BYTES_SPILLED_TO_REMOTE_STORAGE,
    BYTES_SENT_OVER_THE_NETWORK,

    -- Credits and Cost
    CREDITS_USED_CLOUD_SERVICES,

    -- Data Transfer
    OUTBOUND_DATA_TRANSFER_CLOUD,
    OUTBOUND_DATA_TRANSFER_REGION,
    OUTBOUND_DATA_TRANSFER_BYTES,
    INBOUND_DATA_TRANSFER_CLOUD,
    INBOUND_DATA_TRANSFER_REGION,
    INBOUND_DATA_TRANSFER_BYTES,

    -- External Functions
    EXTERNAL_FUNCTION_TOTAL_INVOCATIONS,
    EXTERNAL_FUNCTION_TOTAL_SENT_ROWS,
    EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS,
    EXTERNAL_FUNCTION_TOTAL_SENT_BYTES,
    EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES,

    -- Query Acceleration
    QUERY_ACCELERATION_BYTES_SCANNED,
    QUERY_ACCELERATION_PARTITIONS_SCANNED,
    QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR,

    -- Transaction Information
    TRANSACTION_ID,

    -- System Information
    RELEASE_VERSION,

    -- Performance Ratios and Calculated Fields
    COMPILATION_TIME_PERCENTAGE,
    EXECUTION_TIME_PERCENTAGE,
    ROWS_PER_MB_SCANNED,

    -- Performance Classifications
    PERFORMANCE_CATEGORY,
    CACHE_EFFICIENCY,
    SPILL_CLASSIFICATION,

    -- Analysis metadata
    CURRENT_TIMESTAMP as ANALYSIS_TIMESTAMP,
    CURRENT_DATE - 1 as ANALYSIS_DATE

FROM filtered_queries
ORDER BY START_TIME DESC;

--

CREATE OR REPLACE TABLE user_query_performance_report AS
WITH percentile_reference AS (
    SELECT
        warehouse_size,
        PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY bytes_scanned) AS bytes_scanned_p10,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_elapsed_time) AS execution_time_p90
    FROM snowflake.account_usage.query_history
   WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_DATE)
  AND START_TIME <= CURRENT_DATE

      AND user_name IS NOT NULL
      AND query_type NOT IN ('DESCRIBE', 'SHOW', 'USE')
    GROUP BY warehouse_size
),
query_flags AS (
    SELECT
        qh.query_id,
        qh.warehouse_size,
        qh.bytes_scanned,
        qh.total_elapsed_time AS execution_time_ms,
        qh.compilation_time,
        qh.start_time,
        qh.query_text,
        qh.query_hash,
        qh.query_tag,
        qh.error_code,
        qh.execution_status,
        qh.partitions_scanned,
        qh.partitions_total,
        qh.bytes_spilled_to_local_storage,
        qh.bytes_spilled_to_remote_storage,
        qh.user_name,
        qh.database_name,
        qh.schema_name,
        qh.warehouse_name,
        COALESCE(qh.credits_used_cloud_services, 0) AS credits_used_cloud_services,
        qh.rows_produced,
        qh.rows_deleted,
        qh.rows_inserted,
        qh.rows_updated,
        CASE WHEN qh.warehouse_size IN ('MEDIUM', 'LARGE', 'X-LARGE', '2X-LARGE', '3X-LARGE', '4X-LARGE') AND qh.bytes_scanned < pr.bytes_scanned_p10 THEN 1 ELSE 0 END AS over_provisioned,
        CASE WHEN EXTRACT(HOUR FROM qh.start_time) BETWEEN 9 AND 17 AND qh.total_elapsed_time > 300000 THEN 1 ELSE 0 END AS peak_hour_long_running,
        CASE WHEN qh.query_text ILIKE 'SELECT *%' THEN 1 ELSE 0 END AS select_star,
        CASE WHEN qh.partitions_total > 0 AND qh.partitions_scanned = qh.partitions_total THEN 1 ELSE 0 END AS unpartitioned_scan,
        CASE WHEN qh.query_hash IS NOT NULL THEN 1 ELSE 0 END AS repeated_query,
        CASE WHEN (qh.query_text ILIKE '%JOIN%' AND qh.query_text ILIKE '%JOIN%') OR qh.query_text ILIKE '%WINDOW%' THEN 1 ELSE 0 END AS complex_query,
        CASE WHEN qh.error_code IS NOT NULL OR qh.execution_status IN ('FAILED', 'CANCELLED') THEN 1 ELSE 0 END AS failed_cancelled,
        CASE WHEN qh.bytes_spilled_to_local_storage > 0 OR qh.bytes_spilled_to_remote_storage > 0 THEN 1 ELSE 0 END AS spilled,
        CASE WHEN qh.rows_produced = 0 AND qh.bytes_scanned > 1000000 THEN 1 ELSE 0 END AS zero_result_query,
        CASE WHEN qh.compilation_time > 5000 THEN 1 ELSE 0 END AS high_compile_time,
        CASE WHEN qh.query_tag IS NULL THEN 1 ELSE 0 END AS untagged_query,
        CASE WHEN qh.query_text ILIKE '%ORDER BY%' AND qh.query_text NOT ILIKE '%LIMIT%' THEN 1 ELSE 0 END AS unlimited_order_by,
        CASE WHEN qh.query_text ILIKE '%GROUP BY%' AND qh.rows_produced > 1000000 THEN 1 ELSE 0 END AS large_group_by,
        CASE WHEN qh.total_elapsed_time > pr.execution_time_p90 THEN 1 ELSE 0 END AS slow_query,
        CASE WHEN qh.query_text ILIKE '%DISTINCT%' AND qh.bytes_scanned > 100000000 THEN 1 ELSE 0 END AS expensive_distinct,
        CASE WHEN qh.query_text ILIKE '%LIKE%' AND qh.query_text NOT ILIKE '%INDEX%' THEN 1 ELSE 0 END AS inefficient_like,
        CASE WHEN qh.bytes_scanned > 0 AND qh.rows_produced = 0 THEN 1 ELSE 0 END AS no_results_with_scan,
        CASE WHEN qh.query_text ILIKE '%CROSS JOIN%' OR (qh.query_text ILIKE '%JOIN%' AND qh.query_text NOT ILIKE '%ON%') THEN 1 ELSE 0 END AS cartesian_join,
        CASE WHEN qh.total_elapsed_time > 0 AND qh.compilation_time / qh.total_elapsed_time > 0.5 THEN 1 ELSE 0 END AS high_compile_ratio
    FROM snowflake.account_usage.query_history qh
    LEFT JOIN percentile_reference pr ON qh.warehouse_size = pr.warehouse_size
    WHERE START_TIME >= DATEADD(DAY, -7, CURRENT_DATE)
  AND START_TIME <= CURRENT_DATE

        AND qh.query_type NOT IN ('DESCRIBE', 'SHOW', 'USE')
        AND qh.user_name IS NOT NULL
        AND qh.total_elapsed_time > 1000
),
sample_queries AS (
    SELECT
        user_name,
        'over_provisioned' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY start_time DESC) AS sample_queries
    FROM query_flags WHERE over_provisioned = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'over_provisioned', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(over_provisioned) = 0
    UNION ALL
    SELECT
        user_name,
        'peak_hour_long_running' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY execution_time_ms DESC) AS sample_queries
    FROM query_flags WHERE peak_hour_long_running = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'peak_hour_long_running', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(peak_hour_long_running) = 0
    UNION ALL
    SELECT
        user_name,
        'select_star' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE select_star = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'select_star', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(select_star) = 0
    UNION ALL
    SELECT
        user_name,
        'unpartitioned_scan' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'partitions_scanned', partitions_scanned, 'partitions_total', partitions_total, 'start_time', start_time)) WITHIN GROUP (ORDER BY partitions_scanned DESC) AS sample_queries
    FROM query_flags WHERE unpartitioned_scan = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'unpartitioned_scan', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(unpartitioned_scan) = 0
    UNION ALL
    SELECT
        user_name,
        'spilled' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_spilled_to_local_storage', bytes_spilled_to_local_storage, 'bytes_spilled_to_remote_storage', bytes_spilled_to_remote_storage, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY (bytes_spilled_to_local_storage + bytes_spilled_to_remote_storage) DESC) AS sample_queries
    FROM query_flags WHERE spilled = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'spilled', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(spilled) = 0
    UNION ALL
    SELECT
        user_name,
        'failed_cancelled' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'error_code', error_code, 'execution_status', execution_status, 'start_time', start_time)) WITHIN GROUP (ORDER BY start_time DESC) AS sample_queries
    FROM query_flags WHERE failed_cancelled = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'failed_cancelled', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(failed_cancelled) = 0
    UNION ALL
    SELECT
        user_name,
        'zero_result_query' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'bytes_scanned', bytes_scanned, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE zero_result_query = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'zero_result_query', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(zero_result_query) = 0
    UNION ALL
    SELECT
        user_name,
        'high_compile_time' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'compilation_time', compilation_time, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY compilation_time DESC) AS sample_queries
    FROM query_flags WHERE high_compile_time = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'high_compile_time', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(high_compile_time) = 0
    UNION ALL
    SELECT
        user_name,
        'slow_query' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'warehouse_size', warehouse_size, 'start_time', start_time)) WITHIN GROUP (ORDER BY execution_time_ms DESC) AS sample_queries
    FROM query_flags WHERE slow_query = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'slow_query', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(slow_query) = 0
    UNION ALL
    SELECT
        user_name,
        'cartesian_join' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'rows_produced', rows_produced, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE cartesian_join = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'cartesian_join', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(cartesian_join) = 0
    UNION ALL
    SELECT
        user_name,
        'unlimited_order_by' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE unlimited_order_by = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'unlimited_order_by', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(unlimited_order_by) = 0
    UNION ALL
    SELECT
        user_name,
        'large_group_by' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'rows_produced', rows_produced, 'start_time', start_time)) WITHIN GROUP (ORDER BY rows_produced DESC) AS sample_queries
    FROM query_flags WHERE large_group_by = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'large_group_by', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(large_group_by) = 0
    UNION ALL
    SELECT
        user_name,
        'expensive_distinct' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE expensive_distinct = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'expensive_distinct', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(expensive_distinct) = 0
    UNION ALL
    SELECT
        user_name,
        'inefficient_like' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE inefficient_like = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'inefficient_like', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(inefficient_like) = 0
    UNION ALL
    SELECT
        user_name,
        'no_results_with_scan' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'execution_time_ms', execution_time_ms, 'bytes_scanned', bytes_scanned, 'start_time', start_time)) WITHIN GROUP (ORDER BY bytes_scanned DESC) AS sample_queries
    FROM query_flags WHERE no_results_with_scan = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'no_results_with_scan', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(no_results_with_scan) = 0
    UNION ALL
    SELECT
        user_name,
        'high_compile_ratio' AS flag_type,
        ARRAY_AGG(OBJECT_CONSTRUCT('query_id', query_id, 'query_text', query_text, 'compilation_time', compilation_time, 'execution_time_ms', execution_time_ms, 'start_time', start_time)) WITHIN GROUP (ORDER BY compilation_time DESC) AS sample_queries
    FROM query_flags WHERE high_compile_ratio = 1 GROUP BY user_name HAVING COUNT(*) > 0
    UNION ALL SELECT user_name, 'high_compile_ratio', ARRAY_CONSTRUCT() FROM query_flags GROUP BY user_name HAVING SUM(high_compile_ratio) = 0
),
pivoted_samples AS (
    SELECT
        user_name,
        OBJECT_AGG(flag_type, sample_queries) AS query_samples
    FROM sample_queries
    GROUP BY user_name
),
user_metrics AS (
    SELECT
        user_name,
        COUNT(DISTINCT query_id) AS total_queries,
        COUNT(DISTINCT warehouse_name) AS warehouses_used,
        COUNT(DISTINCT database_name) AS databases_accessed,
        ROUND(SUM(credits_used_cloud_services), 2) AS total_credits,
        ROUND(AVG(execution_time_ms), 2) AS avg_execution_time_ms,
        ROUND(AVG(bytes_scanned / NULLIF(rows_produced, 0)), 2) AS avg_bytes_per_row,
        ROUND(SUM(bytes_scanned) / POWER(1024, 3), 2) AS total_data_scanned_gb,
        ROUND(SUM(failed_cancelled) * 1.0 / NULLIF(COUNT(*), 0) * 100, 2) AS failure_cancellation_rate_pct,
        SUM(spilled) AS spilled_queries,
        SUM(over_provisioned) AS over_provisioned_queries,
        SUM(peak_hour_long_running) AS peak_hour_long_running_queries,
        SUM(select_star) AS select_star_queries,
        SUM(unpartitioned_scan) AS unpartitioned_scan_queries,
        COUNT(*) - COUNT(DISTINCT query_hash) AS repeated_queries,
        SUM(complex_query) AS complex_join_queries,
        SUM(zero_result_query) AS zero_result_queries,
        SUM(high_compile_time) AS high_compile_queries,
        SUM(untagged_query) AS untagged_queries,
        SUM(unlimited_order_by) AS unlimited_order_by_queries,
        SUM(large_group_by) AS large_group_by_queries,
        SUM(slow_query) AS slow_queries,
        SUM(expensive_distinct) AS expensive_distinct_queries,
        SUM(inefficient_like) AS inefficient_like_queries,
        SUM(no_results_with_scan) AS no_results_with_scan_queries,
        SUM(cartesian_join) AS cartesian_join_queries,
        SUM(high_compile_ratio) AS high_compile_ratio_queries,
        ROUND(
            SUM(spilled) * 5 +
            SUM(over_provisioned) * 4 +
            SUM(peak_hour_long_running) * 4 +
            SUM(select_star) * 3 +
            SUM(unpartitioned_scan) * 4 +
            (COUNT(*) - COUNT(DISTINCT query_hash)) * 2 +
            SUM(complex_query) * 3 +
            SUM(failed_cancelled) * 3 +
            SUM(zero_result_query) * 5 +
            SUM(high_compile_time) * 3 +
            SUM(untagged_query) * 2 +
            SUM(unlimited_order_by) * 3 +
            SUM(large_group_by) * 4 +
            SUM(slow_query) * 4 +
            SUM(expensive_distinct) * 4 +
            SUM(inefficient_like) * 2 +
            SUM(no_results_with_scan) * 5 +
            SUM(cartesian_join) * 6 +
            SUM(high_compile_ratio) * 3, 2
        ) AS weighted_score
    FROM query_flags
    GROUP BY user_name
),
cost_percentiles AS (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_credits) AS cost_p90 FROM user_metrics
),
bytes_percentiles AS (
    SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY avg_bytes_per_row) AS bytes_p90 FROM user_metrics
),
failure_percentiles AS (
    SELECT PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY failure_cancellation_rate_pct) AS failure_p80 FROM user_metrics
)
SELECT
    um.user_name,
    um.total_queries,
    um.warehouses_used,
    um.databases_accessed,
    um.total_credits,
    um.avg_execution_time_ms,
    um.avg_bytes_per_row,
    um.total_data_scanned_gb,
    um.failure_cancellation_rate_pct,
    um.spilled_queries,
    um.over_provisioned_queries,
    um.peak_hour_long_running_queries,
    um.select_star_queries,
    um.unpartitioned_scan_queries,
    um.repeated_queries,
    um.complex_join_queries,
    um.zero_result_queries,
    um.high_compile_queries,
    um.untagged_queries,
    um.unlimited_order_by_queries,
    um.large_group_by_queries,
    um.slow_queries,
    um.expensive_distinct_queries,
    um.inefficient_like_queries,
    um.no_results_with_scan_queries,
    um.cartesian_join_queries,
    um.high_compile_ratio_queries,
    um.weighted_score,
    CASE WHEN um.total_credits > cost_percentiles.cost_p90 THEN 'High Cost' ELSE 'Normal' END AS cost_status,
    ARRAY_CONSTRUCT_COMPACT(
        CASE WHEN um.avg_bytes_per_row > bytes_percentiles.bytes_p90 THEN 'Reduce data scanned with clustering or selective columns.' ELSE NULL END,
        CASE WHEN um.failure_cancellation_rate_pct > failure_percentiles.failure_p80 THEN 'Debug query syntax and logic.' ELSE NULL END,
        CASE WHEN um.spilled_queries > 10 THEN 'Optimize memory usage or increase warehouse size.' ELSE NULL END,
        CASE WHEN um.over_provisioned_queries > 10 THEN 'Use smaller warehouses for simple queries.' ELSE NULL END,
        CASE WHEN um.peak_hour_long_running_queries > 10 THEN 'Schedule long queries off-peak.' ELSE NULL END,
        CASE WHEN um.select_star_queries > 10 THEN 'Specify columns instead of SELECT *.' ELSE NULL END,
        CASE WHEN um.unpartitioned_scan_queries > 10 THEN 'Implement partitioning or clustering.' ELSE NULL END,
        CASE WHEN um.repeated_queries > 10 THEN 'Review frequently executed queries.' ELSE NULL END,
        CASE WHEN um.complex_join_queries > 10 THEN 'Simplify complex JOINs or window functions.' ELSE NULL END,
        CASE WHEN um.zero_result_queries > 10 THEN 'Avoid queries that return no data.' ELSE NULL END,
        CASE WHEN um.high_compile_queries > 10 THEN 'Refactor complex SQL to reduce compile time.' ELSE NULL END,
        CASE WHEN um.untagged_queries > 10 THEN 'Apply query tags for cost attribution.' ELSE NULL END,
        CASE WHEN um.unlimited_order_by_queries > 5 THEN 'Add LIMIT clauses to ORDER BY queries.' ELSE NULL END,
        CASE WHEN um.large_group_by_queries > 5 THEN 'Optimize GROUP BY operations with pre-aggregation.' ELSE NULL END,
        CASE WHEN um.slow_queries > 5 THEN 'Optimize slow-running queries.' ELSE NULL END,
        CASE WHEN um.expensive_distinct_queries > 5 THEN 'Replace DISTINCT with GROUP BY where possible.' ELSE NULL END,
        CASE WHEN um.inefficient_like_queries > 10 THEN 'Use proper indexing for LIKE operations.' ELSE NULL END,
        CASE WHEN um.no_results_with_scan_queries > 5 THEN 'Add WHERE clauses to prevent unnecessary scans.' ELSE NULL END,
        CASE WHEN um.cartesian_join_queries > 0 THEN 'Review JOIN conditions to avoid Cartesian products.' ELSE NULL END,
        CASE WHEN um.high_compile_ratio_queries > 5 THEN 'Simplify query complexity to reduce compilation overhead.' ELSE NULL END
    ) AS recommendations,
    COALESCE(ps.query_samples, OBJECT_CONSTRUCT()) AS query_samples
FROM user_metrics um
CROSS JOIN cost_percentiles
CROSS JOIN bytes_percentiles
CROSS JOIN failure_percentiles
LEFT JOIN pivoted_samples ps ON um.user_name = ps.user_name
ORDER BY weighted_score DESC;

cols config

Armaan Khan — Thu, 07 Aug 2025 04:42:46 +0000

{
  "ALL_TABLES_DETAILED_ANALYSIS": [
    "DATABASE_NAME TEXT",
    "TABLE_SCHEMA TEXT",
    "TABLE_NAME TEXT",
    "OWNER TEXT",
    "TABLE_CREATION_DATE TIMESTAMP_LTZ",
    "CURRENT_ROW_COUNT NUMBER",
    "SIZE_GB FLOAT",
    "SIZE_BUCKET TEXT",
    "SNOWFLAKE_TABLE_TYPE TEXT",
    "TIME_TRAVEL_DAYS NUMBER",
    "HAS_TIME_TRAVEL BOOLEAN",
    "IS_EXTERNAL_TABLE BOOLEAN",
    "IS_DYNAMIC_TABLE TEXT",
    "IS_TRANSIENT_TABLE TEXT",
    "HAS_CLUSTERING_KEY BOOLEAN",
    "LAST_ACCESSED_TIMESTAMP TIMESTAMP_LTZ",
    "IS_UNUSED_IN_LAST_6_MONTHS BOOLEAN",
    "TOTAL_QUERIES_ACCESSING_TABLE NUMBER",
    "TOTAL_CLOUD_SERVICE_CREDITS_FOR_TABLE FLOAT",
    "HIGH_SCAN_QUERIES_ACCESSING_TABLE NUMBER",
    "TOTAL_QUERY_ELAPSED_TIME_MS NUMBER",
    "TOTAL_BYTES_SPILLED_LOCAL_FOR_TABLE NUMBER",
    "TOTAL_BYTES_SPILLED_REMOTE_FOR_TABLE NUMBER",
    "DATABASE_FAILSAFE_SIZE_GB FLOAT",
    "ANALYSIS_RUN_TIMESTAMP TIMESTAMP_LTZ"
  ],
  "QUERY_DETAILS_COMPLETE": [
    "QUERY_ID TEXT",
    "QUERY_TEXT TEXT",
    "QUERY_HASH TEXT",
    "QUERY_HASH_VERSION NUMBER",
    "QUERY_PARAMETERIZED_HASH TEXT",
    "QUERY_PARAMETERIZED_HASH_VERSION NUMBER",
    "QUERY_TYPE TEXT",
    "QUERY_TAG TEXT",
    "START_TIME TIMESTAMP_LTZ",
    "END_TIME TIMESTAMP_LTZ",
    "TOTAL_ELAPSED_TIME NUMBER",
    "COMPILATION_TIME NUMBER",
    "EXECUTION_TIME NUMBER",
    "QUEUED_PROVISIONING_TIME NUMBER",
    "QUEUED_REPAIR_TIME NUMBER",
    "QUEUED_OVERLOAD_TIME NUMBER",
    "TRANSACTION_BLOCKED_TIME NUMBER",
    "CHILD_QUERIES_WAIT_TIME NUMBER",
    "QUERY_RETRY_TIME NUMBER",
    "QUERY_RETRY_CAUSE TEXT",
    "FAULT_HANDLING_TIME NUMBER",
    "LIST_EXTERNAL_FILES_TIME NUMBER",
    "USER_NAME TEXT",
    "USER_TYPE TEXT",
    "ROLE_NAME TEXT",
    "ROLE_TYPE TEXT",
    "SECONDARY_ROLE_STATS TEXT",
    "SESSION_ID NUMBER",
    "WAREHOUSE_ID NUMBER",
    "WAREHOUSE_NAME TEXT",
    "WAREHOUSE_SIZE TEXT",
    "WAREHOUSE_TYPE TEXT",
    "CLUSTER_NUMBER NUMBER",
    "QUERY_LOAD_PERCENT NUMBER",
    "DATABASE_ID NUMBER",
    "DATABASE_NAME TEXT",
    "SCHEMA_ID NUMBER",
    "SCHEMA_NAME TEXT",
    "USER_DATABASE_ID NUMBER",
    "USER_DATABASE_NAME TEXT",
    "USER_SCHEMA_ID NUMBER",
    "USER_SCHEMA_NAME TEXT",
    "EXECUTION_STATUS TEXT",
    "ERROR_CODE TEXT",
    "ERROR_MESSAGE TEXT",
    "IS_CLIENT_GENERATED_STATEMENT BOOLEAN",
    "BYTES_SCANNED NUMBER",
    "PERCENTAGE_SCANNED_FROM_CACHE FLOAT",
    "BYTES_WRITTEN NUMBER",
    "BYTES_WRITTEN_TO_RESULT NUMBER",
    "BYTES_READ_FROM_RESULT NUMBER",
    "ROWS_PRODUCED NUMBER",
    "ROWS_WRITTEN_TO_RESULT NUMBER",
    "ROWS_INSERTED NUMBER",
    "ROWS_UPDATED NUMBER",
    "ROWS_DELETED NUMBER",
    "ROWS_UNLOADED NUMBER",
    "BYTES_DELETED NUMBER",
    "PARTITIONS_SCANNED NUMBER",
    "PARTITIONS_TOTAL NUMBER",
    "PARTITION_SCAN_PERCENTAGE FLOAT",
    "BYTES_SPILLED_TO_LOCAL_STORAGE NUMBER",
    "BYTES_SPILLED_TO_REMOTE_STORAGE NUMBER",
    "BYTES_SENT_OVER_THE_NETWORK NUMBER",
    "CREDITS_USED_CLOUD_SERVICES FLOAT",
    "OUTBOUND_DATA_TRANSFER_CLOUD TEXT",
    "OUTBOUND_DATA_TRANSFER_REGION TEXT",
    "OUTBOUND_DATA_TRANSFER_BYTES NUMBER",
    "INBOUND_DATA_TRANSFER_CLOUD TEXT",
    "INBOUND_DATA_TRANSFER_REGION TEXT",
    "INBOUND_DATA_TRANSFER_BYTES NUMBER",
    "EXTERNAL_FUNCTION_TOTAL_INVOCATIONS NUMBER",
    "EXTERNAL_FUNCTION_TOTAL_SENT_ROWS NUMBER",
    "EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS NUMBER",
    "EXTERNAL_FUNCTION_TOTAL_SENT_BYTES NUMBER",
    "EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES NUMBER",
    "QUERY_ACCELERATION_BYTES_SCANNED NUMBER",
    "QUERY_ACCELERATION_PARTITIONS_SCANNED NUMBER",
    "QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR NUMBER",
    "TRANSACTION_ID NUMBER",
    "RELEASE_VERSION TEXT",
    "COMPILATION_TIME_PERCENTAGE FLOAT",
    "EXECUTION_TIME_PERCENTAGE FLOAT",
    "ROWS_PER_MB_SCANNED FLOAT",
    "PERFORMANCE_CATEGORY TEXT",
    "CACHE_EFFICIENCY TEXT",
    "SPILL_CLASSIFICATION TEXT",
    "ANALYSIS_TIMESTAMP TIMESTAMP_LTZ",
    "ANALYSIS_DATE DATE"
  ],
  "QUERY_HISTORY_SUMMARY": [
    "QUERY_ID TEXT",
    "QUERY_HASH TEXT",
    "QUERY_PARAMETERIZED_HASH TEXT",
    "QUERY_TEXT_PREVIEW TEXT",
    "QUERY_TYPE TEXT",
    "QUERY_TAG TEXT",
    "START_TIME TIMESTAMP_LTZ",
    "END_TIME TIMESTAMP_LTZ",
    "TOTAL_ELAPSED_TIME NUMBER",
    "COMPILATION_TIME NUMBER",
    "EXECUTION_TIME NUMBER",
    "USER_NAME TEXT",
    "USER_TYPE TEXT",
    "ROLE_NAME TEXT",
    "ROLE_TYPE TEXT",
    "SESSION_ID NUMBER",
    "WAREHOUSE_ID NUMBER",
    "WAREHOUSE_NAME TEXT",
    "WAREHOUSE_SIZE TEXT",
    "WAREHOUSE_TYPE TEXT",
    "CLUSTER_NUMBER NUMBER",
    "DATABASE_ID NUMBER",
    "DATABASE_NAME TEXT",
    "SCHEMA_ID NUMBER",
    "SCHEMA_NAME TEXT",
    "USER_DATABASE_NAME TEXT",
    "USER_SCHEMA_NAME TEXT",
    "EXECUTION_STATUS TEXT",
    "ERROR_CODE TEXT",
    "ERROR_MESSAGE_PREVIEW TEXT",
    "BYTES_SCANNED NUMBER",
    "PERCENTAGE_SCANNED_FROM_CACHE FLOAT",
    "BYTES_WRITTEN NUMBER",
    "ROWS_PRODUCED NUMBER",
    "ROWS_INSERTED NUMBER",
    "ROWS_UPDATED NUMBER",
    "ROWS_DELETED NUMBER",
    "CREDITS_USED_CLOUD_SERVICES FLOAT",
    "BYTES_SPILLED_TO_LOCAL_STORAGE NUMBER",
    "BYTES_SPILLED_TO_REMOTE_STORAGE NUMBER",
    "PARTITIONS_SCANNED NUMBER",
    "PARTITIONS_TOTAL NUMBER",
    "QUEUED_PROVISIONING_TIME NUMBER",
    "QUEUED_REPAIR_TIME NUMBER",
    "QUEUED_OVERLOAD_TIME NUMBER",
    "TRANSACTION_BLOCKED_TIME NUMBER",
    "DURATION_BUCKET TEXT",
    "CREDIT_BUCKET TEXT",
    "SPILL_STATUS TEXT",
    "QUEUE_STATUS TEXT",
    "ANALYSIS_TIMESTAMP TIMESTAMP_LTZ",
    "ANALYSIS_DATE DATE"
  ],
  "WAREHOUSE_ANALYTICS_DASHBOARD_with_queries": [
    "WAREHOUSE_ID NUMBER(38,0)",
    "WAREHOUSE_NAME VARCHAR(16777216)",
    "WAREHOUSE_SIZE VARCHAR(16777216)",
    "WAREHOUSE_TYPE VARCHAR(16777216)",
    "CLUSTER_COUNT VARCHAR(16777216)",
    "SUSPEND_POLICY VARCHAR(16777216)",
    "MIN_CLUSTER_COUNT VARCHAR(16777216)",
    "MAX_CLUSTER_COUNT VARCHAR(16777216)",
    "QUERIES_1_10_SEC NUMBER(18,0)",
    "QUERIES_10_20_SEC NUMBER(18,0)",
    "QUERIES_20_60_SEC NUMBER(18,0)",
    "QUERIES_1_3_MIN NUMBER(18,0)",
    "QUERIES_3_5_MIN NUMBER(18,0)",
    "QUERIES_5_PLUS_MIN NUMBER(18,0)",
    "QUEUED_1_2_MIN NUMBER(18,0)",
    "QUEUED_2_5_MIN NUMBER(18,0)",
    "QUEUED_5_10_MIN NUMBER(18,0)",
    "QUEUED_10_20_MIN NUMBER(18,0)",
    "QUEUED_20_PLUS_MIN NUMBER(18,0)",
    "QUERIES_SPILLED_LOCAL NUMBER(18,0)",
    "QUERIES_SPILLED_REMOTE NUMBER(18,0)",
    "TOTAL_BYTES_SPILLED_LOCAL NUMBER(38,0)",
    "TOTAL_BYTES_SPILLED_REMOTE NUMBER(38,0)",
    "FAILED_QUERIES NUMBER(18,0)",
    "SUCCESSFUL_QUERIES NUMBER(18,0)",
    "RUNNING_QUERIES NUMBER(18,0)",
    "QUERIES_0_20_CENTS NUMBER(18,0)",
    "QUERIES_20_40_CENTS NUMBER(18,0)",
    "QUERIES_40_60_CENTS NUMBER(18,0)",
    "QUERIES_60_80_CENTS NUMBER(18,0)",
    "QUERIES_80_100_CENTS NUMBER(18,0)",
    "QUERIES_100_PLUS_CENTS NUMBER(18,0)",
    "QUERY_IDS OBJECT",
    "TOTAL_QUERIES NUMBER(18,0)",
    "TOTAL_CREDITS_USED NUMBER(38,9)",
    "TOTAL_COMPUTE_CREDITS NUMBER(38,9)",
    "TOTAL_CLOUD_SERVICES_CREDITS NUMBER(38,9)",
    "ANALYSIS_TIMESTAMP TIMESTAMP_LTZ(9)",
    "ANALYSIS_DATE DATE"
  ],
  "user_query_performance_report": [
    "USER_NAME VARCHAR(16777216)",
    "TOTAL_QUERIES NUMBER(18,0)",
    "WAREHOUSES_USED NUMBER(18,0)",
    "DATABASES_ACCESSED NUMBER(18,0)",
    "TOTAL_CREDITS FLOAT",
    "AVG_EXECUTION_TIME_MS NUMBER(38,2)",
    "AVG_BYTES_PER_ROW NUMBER(38,2)",
    "TOTAL_DATA_SCANNED_GB FLOAT",
    "FAILURE_CANCELLATION_RATE_PCT NUMBER(24,2)",
    "SPILLED_QUERIES NUMBER(13,0)",
    "OVER_PROVISIONED_QUERIES NUMBER(13,0)",
    "PEAK_HOUR_LONG_RUNNING_QUERIES NUMBER(13,0)",
    "SELECT_STAR_QUERIES NUMBER(13,0)",
    "UNPARTITIONED_SCAN_QUERIES NUMBER(13,0)",
    "REPEATED_QUERIES NUMBER(19,0)",
    "COMPLEX_JOIN_QUERIES NUMBER(13,0)",
    "ZERO_RESULT_QUERIES NUMBER(13,0)",
    "HIGH_COMPILE_QUERIES NUMBER(13,0)",
    "UNTAGGED_QUERIES NUMBER(13,0)",
    "UNLIMITED_ORDER_BY_QUERIES NUMBER(13,0)",
    "LARGE_GROUP_BY_QUERIES NUMBER(13,0)",
    "SLOW_QUERIES NUMBER(13,0)",
    "EXPENSIVE_DISTINCT_QUERIES NUMBER(13,0)",
    "INEFFICIENT_LIKE_QUERIES NUMBER(13,0)",
    "NO_RESULTS_WITH_SCAN_QUERIES NUMBER(13,0)",
    "CARTESIAN_JOIN_QUERIES NUMBER(13,0)",
    "HIGH_COMPILE_RATIO_QUERIES NUMBER(13,0)",
    "WEIGHTED_SCORE NUMBER(34,0)",
    "COST_STATUS VARCHAR(9)",
    "RECOMMENDATIONS ARRAY",
    "QUERY_SAMPLES OBJECT"
  ],
  "dashboard_analytic": [
    "NAME VARCHAR(16777216)",
    "TYPE VARCHAR(8)",
    "OWNER VARCHAR(16777216)",
    "SIZE_GB FLOAT",
    "COUNT_0_1_GB NUMBER(13,0)",
    "SIZE_0_1_GB FLOAT",
    "COUNT_1_5_GB NUMBER(13,0)",
    "SIZE_1_5_GB FLOAT",
    "COUNT_5_PLUS_GB NUMBER(13,0)",
    "SIZE_5_PLUS_GB FLOAT",
    "FAILSAFE_SIZE_GB FLOAT",
    "TIME_TRAVEL_TABLES NUMBER(13,0)",
    "UNUSED_TABLES NUMBER(13,0)",
    "EXTERNAL_TABLES NUMBER(13,0)",
    "MATERIALIZED_VIEWS NUMBER(13,0)",
    "DYNAMIC_TABLES NUMBER(13,0)",
    "RECOMMENDED_TRANSIENT_TABLES NUMBER(13,0)",
    "AUTO_CLUSTERING_TABLES NUMBER(13,0)",
    "POTENTIAL_STORAGE_SAVED VARCHAR(3)",
    "CREDITS_USED FLOAT",
    "HIGH_SCAN_QUERIES NUMBER(13,0)",
    "TABLES_RECOMMENDED_FOR_CLUSTERING ARRAY"
  ]
}

{
"1-10_SECONDS_QUERIES": ["query_id1", "query_id2"],
"10-20_SECONDS_QUERIES": [],
"20-60_SECONDS_QUERIES": ["query_id3"],
"1-3_MINUTES_QUERIES": [],
"3-5_MINUTES_QUERIES": ["query_id4"],
"5_PLUS_MINUTES_QUERIES": [],
"1-2_MINUTES_QUEUED": [],
"2-5_MINUTES_QUEUED": ["query_id5"],
"5-10_MINUTES_QUEUED": [],
"10-20_MINUTES_QUEUED": [],
"20_PLUS_MINUTES_QUEUED": [],
"SPILLED_LOCAL_QUERIES": ["query_id1"],
"SPILLED_REMOTE_QUERIES": [],
"FAILED_QUERIES": [],
"SUCCESSFUL_QUERIES": ["query_id1", "query_id2", "query_id3", "query_id4"],
"RUNNING_QUERIES": ["query_id5"],
"0-20_CENTS_QUERIES": ["query_id1", "query_id2"],
"20-40_CENTS_QUERIES": [],
"40-60_CENTS_QUERIES": ["query_id3"],
"60-80_CENTS_QUERIES": [],
"80-100_CENTS_QUERIES": [],
"100_PLUS_CENTS_QUERIES": ["query_id4", "query_id5"]
}

snowfalke views

Armaan Khan — Tue, 05 Aug 2025 03:18:33 +0000

SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  QUERY_START_TIME
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  USER_NAME
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  DIRECT_OBJECTS_ACCESSED
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  BASE_OBJECTS_ACCESSED
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  OBJECTS_MODIFIED
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  OBJECT_MODIFIED_BY_DDL
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  POLICIES_REFERENCED
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  PARENT_QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   ACCESS_HISTORY  ROOT_QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    START_TIME
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    END_TIME
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    NUM_BYTES_RECLUSTERED
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    NUM_ROWS_RECLUSTERED
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    TABLE_ID
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    TABLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    SCHEMA_NAME
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   AUTOMATIC_CLUSTERING_HISTORY    INSTANCE_ID
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLUMN_ID
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLUMN_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_ID
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_CATALOG_ID
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS TABLE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS ORDINAL_POSITION
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLUMN_DEFAULT
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IS_NULLABLE
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DATA_TYPE
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS CHARACTER_MAXIMUM_LENGTH
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS CHARACTER_OCTET_LENGTH
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS NUMERIC_PRECISION
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS NUMERIC_PRECISION_RADIX
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS NUMERIC_SCALE
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DATETIME_PRECISION
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS INTERVAL_TYPE
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS INTERVAL_PRECISION
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS CHARACTER_SET_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS CHARACTER_SET_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS CHARACTER_SET_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLLATION_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLLATION_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COLLATION_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DOMAIN_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DOMAIN_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DOMAIN_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS UDT_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS UDT_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS UDT_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS SCOPE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS SCOPE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS SCOPE_NAME
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS MAXIMUM_CARDINALITY
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DTD_IDENTIFIER
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IS_SELF_REFERENCING
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IS_IDENTITY
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_GENERATION
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_START
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_INCREMENT
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_MAXIMUM
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_MINIMUM
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_CYCLE
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS IDENTITY_ORDERED
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS SCHEMA_EVOLUTION_RECORD
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS COMMENT
SNOWFLAKE   ACCOUNT_USAGE   COLUMNS DELETED
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  USAGE_DATE
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  DELETED
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  AVERAGE_DATABASE_BYTES
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  AVERAGE_FAILSAFE_BYTES
SNOWFLAKE   ACCOUNT_USAGE   DATABASE_STORAGE_USAGE_HISTORY  AVERAGE_HYBRID_TABLE_STORAGE_BYTES
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   START_TIME
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   END_TIME
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   SOURCE_CLOUD
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   SOURCE_REGION
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   TARGET_CLOUD
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   TARGET_REGION
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   BYTES_TRANSFERRED
SNOWFLAKE   ACCOUNT_USAGE   DATA_TRANSFER_HISTORY   TRANSFER_TYPE
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES CREATED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES MODIFIED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES PRIVILEGE
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANTED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES NAME
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES TABLE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES TABLE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANTED_TO
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANTEE_NAME
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANT_OPTION
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANTED_BY
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES DELETED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES GRANTED_BY_ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_ROLES OBJECT_INSTANCE
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS CREATED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS DELETED_ON
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS ROLE
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS GRANTED_TO
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS GRANTEE_NAME
SNOWFLAKE   ACCOUNT_USAGE   GRANTS_TO_USERS GRANTED_BY
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   EVENT_ID
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   EVENT_TIMESTAMP
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   EVENT_TYPE
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   USER_NAME
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   CLIENT_IP
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   REPORTED_CLIENT_TYPE
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   REPORTED_CLIENT_VERSION
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   FIRST_AUTHENTICATION_FACTOR
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   SECOND_AUTHENTICATION_FACTOR
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   IS_SUCCESS
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   ERROR_CODE
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   ERROR_MESSAGE
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   RELATED_EVENT_ID
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   CONNECTION
SNOWFLAKE   ACCOUNT_USAGE   LOGIN_HISTORY   CLIENT_PRIVATE_LINK_ID
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    SERVICE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    START_TIME
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    END_TIME
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    ENTITY_ID
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    NAME
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    CREDITS_USED_COMPUTE
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    CREDITS_USED_CLOUD_SERVICES
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    BYTES
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    ROWS
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    FILES
SNOWFLAKE   ACCOUNT_USAGE   METERING_HISTORY    BUDGET_ID
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCED_DATABASE
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCED_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCED_OBJECT_NAME
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCED_OBJECT_ID
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCED_OBJECT_DOMAIN
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCING_DATABASE
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCING_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCING_OBJECT_NAME
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCING_OBJECT_ID
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES REFERENCING_OBJECT_DOMAIN
SNOWFLAKE   ACCOUNT_USAGE   OBJECT_DEPENDENCIES DEPENDENCY_TYPE
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  PIPE_ID
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  PIPE_NAME
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  START_TIME
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  END_TIME
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  BYTES_INSERTED
SNOWFLAKE   ACCOUNT_USAGE   PIPE_USAGE_HISTORY  FILES_INSERTED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_ACCELERATION_HISTORY  START_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_ACCELERATION_HISTORY  END_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_ACCELERATION_HISTORY  CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_ACCELERATION_HISTORY  WAREHOUSE_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_ACCELERATION_HISTORY  WAREHOUSE_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_TEXT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   SCHEMA_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_TYPE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   SESSION_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   WAREHOUSE_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   WAREHOUSE_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   WAREHOUSE_SIZE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   WAREHOUSE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   CLUSTER_NUMBER
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_TAG
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXECUTION_STATUS
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ERROR_CODE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ERROR_MESSAGE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   START_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   END_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   TOTAL_ELAPSED_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_SCANNED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   PERCENTAGE_SCANNED_FROM_CACHE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_WRITTEN
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_WRITTEN_TO_RESULT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_READ_FROM_RESULT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_PRODUCED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_INSERTED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_UPDATED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_DELETED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_UNLOADED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_DELETED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   PARTITIONS_SCANNED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   PARTITIONS_TOTAL
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_SPILLED_TO_LOCAL_STORAGE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_SPILLED_TO_REMOTE_STORAGE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   BYTES_SENT_OVER_THE_NETWORK
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   COMPILATION_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXECUTION_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUEUED_PROVISIONING_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUEUED_REPAIR_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUEUED_OVERLOAD_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   TRANSACTION_BLOCKED_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   OUTBOUND_DATA_TRANSFER_CLOUD
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   OUTBOUND_DATA_TRANSFER_REGION
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   OUTBOUND_DATA_TRANSFER_BYTES
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   INBOUND_DATA_TRANSFER_CLOUD
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   INBOUND_DATA_TRANSFER_REGION
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   INBOUND_DATA_TRANSFER_BYTES
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   LIST_EXTERNAL_FILES_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   CREDITS_USED_CLOUD_SERVICES
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   RELEASE_VERSION
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXTERNAL_FUNCTION_TOTAL_INVOCATIONS
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXTERNAL_FUNCTION_TOTAL_SENT_ROWS
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXTERNAL_FUNCTION_TOTAL_RECEIVED_ROWS
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXTERNAL_FUNCTION_TOTAL_SENT_BYTES
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   EXTERNAL_FUNCTION_TOTAL_RECEIVED_BYTES
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_LOAD_PERCENT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   IS_CLIENT_GENERATED_STATEMENT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_ACCELERATION_BYTES_SCANNED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_ACCELERATION_PARTITIONS_SCANNED
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_ACCELERATION_UPPER_LIMIT_SCALE_FACTOR
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   TRANSACTION_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   CHILD_QUERIES_WAIT_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_HASH
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_HASH_VERSION
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_PARAMETERIZED_HASH
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_PARAMETERIZED_HASH_VERSION
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   SECONDARY_ROLE_STATS
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   ROWS_WRITTEN_TO_RESULT
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_RETRY_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   QUERY_RETRY_CAUSE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   FAULT_HANDLING_TIME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_TYPE
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_SCHEMA_NAME
SNOWFLAKE   ACCOUNT_USAGE   QUERY_HISTORY   USER_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CONSTRAINT_CATALOG_ID
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CONSTRAINT_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CONSTRAINT_NAME
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_CATALOG_ID
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_NAME
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS MATCH_OPTION
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS UPDATE_RULE
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS DELETE_RULE
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS COMMENT
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS CREATED
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS LAST_ALTERED
SNOWFLAKE   ACCOUNT_USAGE   REFERENTIAL_CONSTRAINTS DELETED
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   START_TIME
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   END_TIME
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   REPLICATION_USAGE_HISTORY   BYTES_TRANSFERRED
SNOWFLAKE   ACCOUNT_USAGE   ROLES   ROLE_ID
SNOWFLAKE   ACCOUNT_USAGE   ROLES   CREATED_ON
SNOWFLAKE   ACCOUNT_USAGE   ROLES   DELETED_ON
SNOWFLAKE   ACCOUNT_USAGE   ROLES   NAME
SNOWFLAKE   ACCOUNT_USAGE   ROLES   COMMENT
SNOWFLAKE   ACCOUNT_USAGE   ROLES   OWNER
SNOWFLAKE   ACCOUNT_USAGE   ROLES   ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   ROLES   ROLE_DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   ROLES   ROLE_INSTANCE_ID
SNOWFLAKE   ACCOUNT_USAGE   ROLES   OWNER_ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    SESSION_ID
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CREATED_ON
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    USER_NAME
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    AUTHENTICATION_METHOD
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    LOGIN_EVENT_ID
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLIENT_APPLICATION_VERSION
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLIENT_APPLICATION_ID
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLIENT_ENVIRONMENT
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLIENT_BUILD_ID
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLIENT_VERSION
SNOWFLAKE   ACCOUNT_USAGE   SESSIONS    CLOSED_REASON
SNOWFLAKE   ACCOUNT_USAGE   STAGE_STORAGE_USAGE_HISTORY USAGE_DATE
SNOWFLAKE   ACCOUNT_USAGE   STAGE_STORAGE_USAGE_HISTORY AVERAGE_STAGE_BYTES
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_ID
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_CATALOG_ID
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_OWNER
SNOWFLAKE   ACCOUNT_USAGE   TABLES  TABLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_TRANSIENT
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_ICEBERG
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_DYNAMIC
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_HYBRID
SNOWFLAKE   ACCOUNT_USAGE   TABLES  CLUSTERING_KEY
SNOWFLAKE   ACCOUNT_USAGE   TABLES  ROW_COUNT
SNOWFLAKE   ACCOUNT_USAGE   TABLES  BYTES
SNOWFLAKE   ACCOUNT_USAGE   TABLES  RETENTION_TIME
SNOWFLAKE   ACCOUNT_USAGE   TABLES  SELF_REFERENCING_COLUMN_NAME
SNOWFLAKE   ACCOUNT_USAGE   TABLES  REFERENCE_GENERATION
SNOWFLAKE   ACCOUNT_USAGE   TABLES  USER_DEFINED_TYPE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   TABLES  USER_DEFINED_TYPE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   TABLES  USER_DEFINED_TYPE_NAME
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_INSERTABLE_INTO
SNOWFLAKE   ACCOUNT_USAGE   TABLES  IS_TYPED
SNOWFLAKE   ACCOUNT_USAGE   TABLES  COMMIT_ACTION
SNOWFLAKE   ACCOUNT_USAGE   TABLES  CREATED
SNOWFLAKE   ACCOUNT_USAGE   TABLES  LAST_ALTERED
SNOWFLAKE   ACCOUNT_USAGE   TABLES  LAST_DDL
SNOWFLAKE   ACCOUNT_USAGE   TABLES  LAST_DDL_BY
SNOWFLAKE   ACCOUNT_USAGE   TABLES  DELETED
SNOWFLAKE   ACCOUNT_USAGE   TABLES  AUTO_CLUSTERING_ON
SNOWFLAKE   ACCOUNT_USAGE   TABLES  COMMENT
SNOWFLAKE   ACCOUNT_USAGE   TABLES  OWNER_ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   TABLES  INSTANCE_ID
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  TAG_DATABASE
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  TAG_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  TAG_ID
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  TAG_NAME
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  TAG_VALUE
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  OBJECT_DATABASE
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  OBJECT_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  OBJECT_ID
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  OBJECT_NAME
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  OBJECT_DELETED
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  DOMAIN
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  COLUMN_ID
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  COLUMN_NAME
SNOWFLAKE   ACCOUNT_USAGE   TAG_REFERENCES  APPLY_METHOD
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    NAME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_TEXT
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    CONDITION_TEXT
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    SCHEMA_NAME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    TASK_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    TASK_DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    SCHEDULED_TIME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    COMPLETED_TIME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    STATE
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    RETURN_VALUE
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_START_TIME
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    ERROR_CODE
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    ERROR_MESSAGE
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    GRAPH_VERSION
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    RUN_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    ROOT_TASK_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    SCHEDULED_FROM
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    INSTANCE_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    ATTEMPT_NUMBER
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    CONFIG
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_HASH
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_HASH_VERSION
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_PARAMETERIZED_HASH
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    QUERY_PARAMETERIZED_HASH_VERSION
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    GRAPH_RUN_GROUP_ID
SNOWFLAKE   ACCOUNT_USAGE   TASK_HISTORY    BACKFILL_INFO
SNOWFLAKE   ACCOUNT_USAGE   USERS   USER_ID
SNOWFLAKE   ACCOUNT_USAGE   USERS   NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   CREATED_ON
SNOWFLAKE   ACCOUNT_USAGE   USERS   DELETED_ON
SNOWFLAKE   ACCOUNT_USAGE   USERS   LOGIN_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   DISPLAY_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   FIRST_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   LAST_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   EMAIL
SNOWFLAKE   ACCOUNT_USAGE   USERS   MUST_CHANGE_PASSWORD
SNOWFLAKE   ACCOUNT_USAGE   USERS   HAS_PASSWORD
SNOWFLAKE   ACCOUNT_USAGE   USERS   COMMENT
SNOWFLAKE   ACCOUNT_USAGE   USERS   DISABLED
SNOWFLAKE   ACCOUNT_USAGE   USERS   SNOWFLAKE_LOCK
SNOWFLAKE   ACCOUNT_USAGE   USERS   DEFAULT_WAREHOUSE
SNOWFLAKE   ACCOUNT_USAGE   USERS   DEFAULT_NAMESPACE
SNOWFLAKE   ACCOUNT_USAGE   USERS   DEFAULT_ROLE
SNOWFLAKE   ACCOUNT_USAGE   USERS   EXT_AUTHN_DUO
SNOWFLAKE   ACCOUNT_USAGE   USERS   EXT_AUTHN_UID
SNOWFLAKE   ACCOUNT_USAGE   USERS   HAS_MFA
SNOWFLAKE   ACCOUNT_USAGE   USERS   BYPASS_MFA_UNTIL
SNOWFLAKE   ACCOUNT_USAGE   USERS   LAST_SUCCESS_LOGIN
SNOWFLAKE   ACCOUNT_USAGE   USERS   EXPIRES_AT
SNOWFLAKE   ACCOUNT_USAGE   USERS   LOCKED_UNTIL_TIME
SNOWFLAKE   ACCOUNT_USAGE   USERS   HAS_RSA_PUBLIC_KEY
SNOWFLAKE   ACCOUNT_USAGE   USERS   PASSWORD_LAST_SET_TIME
SNOWFLAKE   ACCOUNT_USAGE   USERS   OWNER
SNOWFLAKE   ACCOUNT_USAGE   USERS   DEFAULT_SECONDARY_ROLE
SNOWFLAKE   ACCOUNT_USAGE   USERS   TYPE
SNOWFLAKE   ACCOUNT_USAGE   USERS   DATABASE_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   DATABASE_ID
SNOWFLAKE   ACCOUNT_USAGE   USERS   SCHEMA_NAME
SNOWFLAKE   ACCOUNT_USAGE   USERS   SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_ID
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_SCHEMA_ID
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_SCHEMA
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_CATALOG_ID
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_CATALOG
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   TABLE_OWNER
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   VIEW_DEFINITION
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   CHECK_OPTION
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   IS_UPDATABLE
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   INSERTABLE_INTO
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   IS_SECURE
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   CREATED
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   LAST_ALTERED
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   LAST_DDL
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   LAST_DDL_BY
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   DELETED
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   COMMENT
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   OWNER_ROLE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   VIEWS   INSTANCE_ID
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    TIMESTAMP
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    WAREHOUSE_ID
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    WAREHOUSE_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    CLUSTER_NUMBER
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    EVENT_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    EVENT_REASON
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    EVENT_STATE
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    USER_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    ROLE_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    QUERY_ID
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    SIZE
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    CLUSTER_COUNT
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    WAREHOUSE_TYPE
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_EVENTS_HISTORY    RESOURCE_CONSTRAINT
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  START_TIME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  END_TIME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  WAREHOUSE_ID
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  WAREHOUSE_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  AVG_RUNNING
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  AVG_QUEUED_LOAD
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  AVG_QUEUED_PROVISIONING
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_LOAD_HISTORY  AVG_BLOCKED
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  START_TIME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  END_TIME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  WAREHOUSE_ID
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  WAREHOUSE_NAME
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  CREDITS_USED
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  CREDITS_USED_COMPUTE
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  CREDITS_USED_CLOUD_SERVICES
SNOWFLAKE   ACCOUNT_USAGE   WAREHOUSE_METERING_HISTORY  CREDITS_ATTRIBUTED_COMPUTE_QUERIES
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS TABLE_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS TABLE_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS TABLE_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COLUMN_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS ORDINAL_POSITION
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COLUMN_DEFAULT
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IS_NULLABLE
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DATA_TYPE
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS CHARACTER_MAXIMUM_LENGTH
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS CHARACTER_OCTET_LENGTH
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS NUMERIC_PRECISION
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS NUMERIC_PRECISION_RADIX
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS NUMERIC_SCALE
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DATETIME_PRECISION
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS INTERVAL_TYPE
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS INTERVAL_PRECISION
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS CHARACTER_SET_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS CHARACTER_SET_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS CHARACTER_SET_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COLLATION_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COLLATION_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COLLATION_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DOMAIN_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DOMAIN_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DOMAIN_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS UDT_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS UDT_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS UDT_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS SCOPE_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS SCOPE_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS SCOPE_NAME
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS MAXIMUM_CARDINALITY
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS DTD_IDENTIFIER
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IS_SELF_REFERENCING
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IS_IDENTITY
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_GENERATION
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_START
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_INCREMENT
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_MAXIMUM
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_MINIMUM
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_CYCLE
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS IDENTITY_ORDERED
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS SCHEMA_EVOLUTION_RECORD
SNOWFLAKE   INFORMATION_SCHEMA  COLUMNS COMMENT
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   GRANTOR
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   GRANTEE
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   GRANTED_TO
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   OBJECT_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   OBJECT_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   OBJECT_NAME
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   OBJECT_TYPE
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   PRIVILEGE_TYPE
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   IS_GRANTABLE
SNOWFLAKE   INFORMATION_SCHEMA  OBJECT_PRIVILEGES   CREATED
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS CONSTRAINT_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS CONSTRAINT_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS CONSTRAINT_NAME
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS UNIQUE_CONSTRAINT_NAME
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS MATCH_OPTION
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS UPDATE_RULE
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS DELETE_RULE
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS COMMENT
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS CREATED
SNOWFLAKE   INFORMATION_SCHEMA  REFERENTIAL_CONSTRAINTS LAST_ALTERED
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  TABLE_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  TABLE_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  TABLE_NAME
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  TABLE_OWNER
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  TABLE_TYPE
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_TRANSIENT
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  CLUSTERING_KEY
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  ROW_COUNT
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  BYTES
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  RETENTION_TIME
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  SELF_REFERENCING_COLUMN_NAME
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  REFERENCE_GENERATION
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  USER_DEFINED_TYPE_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  USER_DEFINED_TYPE_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  USER_DEFINED_TYPE_NAME
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_INSERTABLE_INTO
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_TYPED
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  COMMIT_ACTION
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  CREATED
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  LAST_ALTERED
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  LAST_DDL
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  LAST_DDL_BY
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  AUTO_CLUSTERING_ON
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  COMMENT
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_TEMPORARY
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_ICEBERG
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_DYNAMIC
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_IMMUTABLE
SNOWFLAKE   INFORMATION_SCHEMA  TABLES  IS_HYBRID
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   TABLE_CATALOG
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   TABLE_SCHEMA
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   TABLE_NAME
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   TABLE_OWNER
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   VIEW_DEFINITION
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   CHECK_OPTION
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   IS_UPDATABLE
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   INSERTABLE_INTO
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   IS_SECURE
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   CREATED
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   LAST_ALTERED
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   LAST_DDL
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   LAST_DDL_BY
SNOWFLAKE   INFORMATION_SCHEMA  VIEWS   COMMENT

finall

Armaan Khan — Wed, 30 Jul 2025 07:04:37 +0000

import ast
import json
from datetime import datetime

import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import streamlit as st
from st_aggrid import AgGrid, DataReturnMode, GridOptionsBuilder, GridUpdateMode, JsCode

from core.query_executor import query_executor
from queries.filter import CommonUI
from queries.final_last import USER_360_QUERIES
from queries.QueryBuilder import QueryBuilder


def create_snowflake_dashboard(df):
    # Custom CSS for enhanced styling
    st.markdown(
        """
    <style>
        .main-header {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 50%, #667eea 100%);
            color: white;
            padding: 30px;
            border-radius: 15px;
            text-align: center;
            margin-bottom: 30px;
            box-shadow: 0 8px 25px rgba(102, 126, 234, 0.4);
            animation: gradient 15s ease infinite;
            background-size: 400% 400%;
        }

        @keyframes gradient {
            0% { background-position: 0% 50%; }
            50% { background-position: 100% 50%; }
            100% { background-position: 0% 50%; }
        }

        .metric-card {
            background: linear-gradient(145deg, #ffffff, #f0f2f6);
            padding: 25px;
            border-radius: 15px;
            box-shadow: 0 8px 20px rgba(0,0,0,0.1);
            border: 1px solid rgba(102, 126, 234, 0.1);
            margin: 15px 0;
            transition: all 0.3s ease;
            position: relative;
            overflow: hidden;
        }

        .metric-card::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 4px;
            background: linear-gradient(90deg, #667eea, #764ba2);
        }

        .metric-card:hover {
            transform: translateY(-5px) scale(1.02);
            box-shadow: 0 12px 30px rgba(102, 126, 234, 0.2);
        }

        .metric-value {
            font-size: 2.5em;
            font-weight: 700;
            color: #2c3e50;
            margin-bottom: 8px;
            text-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }

        .metric-label {
            color: #5a6c7d;
            font-size: 1.1em;
            font-weight: 500;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }

        .back-button {
            background: linear-gradient(135deg, #667eea, #764ba2);
            color: white;
            border: none;
            padding: 15px 30px;
            border-radius: 25px;
            font-size: 16px;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.3s ease;
            box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3);
            margin-bottom: 20px;
        }

        .back-button:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.4);
        }

        .query-detail-container {
            background: linear-gradient(145deg, #ffffff, #f8f9fa);
            border-radius: 20px;
            padding: 30px;
            box-shadow: 0 10px 30px rgba(0,0,0,0.15);
            margin: 20px 0;
            border: 1px solid rgba(102, 126, 234, 0.1);
        }

        .query-card {
            background: white;
            border-radius: 12px;
            padding: 20px;
            margin: 15px 0;
            box-shadow: 0 4px 15px rgba(0,0,0,0.08);
            border-left: 4px solid #667eea;
            transition: all 0.3s ease;
        }

        .query-card:hover {
            transform: translateX(5px);
            box-shadow: 0 6px 20px rgba(0,0,0,0.12);
        }

        .status-optimal {
            background: linear-gradient(135deg, #d4edda, #c3e6cb);
            color: #155724;
            padding: 8px 16px;
            border-radius: 20px;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }

        .status-warning {
            background: linear-gradient(135deg, #fff3cd, #ffeaa7);
            color: #856404;
            padding: 8px 16px;
            border-radius: 20px;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }

        .status-critical {
            background: linear-gradient(135deg, #f8d7da, #fab1a0);
            color: #721c24;
            padding: 8px 16px;
            border-radius: 20px;
            font-weight: 600;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }

        .recommendation-card {
            background: linear-gradient(135deg, #fff3cd, #ffeaa7);
            padding: 20px;
            border-radius: 12px;
            margin: 15px 0;
            border-left: 4px solid #ffc107;
            box-shadow: 0 4px 15px rgba(255, 193, 7, 0.2);
        }

        .metric-mini-card {
            background: linear-gradient(145deg, #f8f9fa, #e9ecef);
            border-radius: 10px;
            padding: 15px;
            text-align: center;
            margin: 5px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.05);
            border: 1px solid rgba(102, 126, 234, 0.1);
        }

        .section-title {
            font-size: 1.8em;
            font-weight: 700;
            color: #2c3e50;
            margin: 30px 0 20px 0;
            position: relative;
            padding-bottom: 10px;
        }

        .section-title::after {
            content: '';
            position: absolute;
            bottom: 0;
            left: 0;
            width: 50px;
            height: 3px;
            background: linear-gradient(90deg, #667eea, #764ba2);
            border-radius: 2px;
        }

        .stButton > button {
            background: linear-gradient(135deg, #667eea, #764ba2);
            color: white;
            border: none;
            border-radius: 10px;
            padding: 12px 24px;
            font-weight: 600;
            transition: all 0.3s ease;
            box-shadow: 0 4px 15px rgba(102, 126, 234, 0.3);
        }

        .stButton > button:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.4);
        }

        .ag-theme-streamlit {
            --ag-header-background-color: linear-gradient(135deg, #667eea, #764ba2);
            --ag-header-foreground-color: white;
            --ag-odd-row-background-color: #f8f9fa;
            --ag-row-hover-color: rgba(102, 126, 234, 0.1);
        }

        .fade-in {
            animation: fadeIn 0.5s ease-in;
        }

        @keyframes fadeIn {
            from { opacity: 0; transform: translateY(20px); }
            to { opacity: 1; transform: translateY(0); }
        }
    </style>
    """,
        unsafe_allow_html=True,
    )

    # Initialize session state
    if "view_mode" not in st.session_state:
        st.session_state.view_mode = "table"  # "table" or "details"
    if "selected_user" not in st.session_state:
        st.session_state.selected_user = None
    if "selected_metric" not in st.session_state:
        st.session_state.selected_metric = None

    # Header
    st.markdown(
        """
    <div class="main-header fade-in">
        <h1>❄️ Snowflake Query Analytics Dashboard</h1>
        <p style="font-size: 1.2em; margin-top: 15px;">Monitor, analyze, and optimize your Snowflake query performance with intelligent insights</p>
    </div>
    """,
        unsafe_allow_html=True,
    )

    # Sidebar for data input
    with st.sidebar:
        st.header("📊 Data Management")
        uploaded_file = st.file_uploader("Upload JSON Data", type=["json"])
        if uploaded_file is not None:
            try:
                new_data = json.load(uploaded_file)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                    st.success("✅ Data loaded successfully!")
                else:
                    df = pd.DataFrame([new_data])
                    st.success("✅ Data loaded successfully!")
            except Exception as e:
                st.error(f"❌ Error loading data: {e}")

        st.subheader("Or paste JSON data:")
        json_input = st.text_area(
            "JSON Data", height=200, placeholder="Paste your JSON data here..."
        )
        if st.button("Load JSON Data"):
            try:
                new_data = json.loads(json_input)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                else:
                    df = pd.DataFrame([new_data])
                st.success("✅ Data loaded successfully!")
                st.rerun()
            except Exception as e:
                st.error(f"❌ Error parsing JSON: {e}")

    if not df.empty:
        # Show different views based on mode
        if st.session_state.view_mode == "table":
            show_table_view(df)
        else:
            show_details_view(df)
    else:
        show_empty_state()


def show_table_view(df):
    """Display the main table view with metrics and AgGrid table"""

    # Overall metrics cards
    st.markdown(
        '<div class="section-title fade-in">📈 Overall Performance Metrics</div>',
        unsafe_allow_html=True,
    )

    # First row of metrics
    col1, col2, col3, col4 = st.columns(4)
    with col1:
        total_users = len(df)
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{total_users}</div>
            <div class="metric-label">👥 Total Users</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col2:
        total_queries = df["TOTAL_QUERIES"].sum()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{total_queries:,}</div>
            <div class="metric-label">🔍 Total Queries</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col3:
        total_credits = df["TOTAL_CREDITS"].sum()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">${total_credits:,.2f}</div>
            <div class="metric-label">💰 Total Credits</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col4:
        avg_score = df["WEIGHTED_SCORE"].mean()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{avg_score:.1f}</div>
            <div class="metric-label">⭐ Avg Score</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    # Second row of metrics
    col5, col6, col7, col8 = st.columns(4)
    with col5:
        total_spilled = df["SPILLED_QUERIES"].sum()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{total_spilled}</div>
            <div class="metric-label">💾 Spilled Queries</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col6:
        total_slow = df["SLOW_QUERIES"].sum()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{total_slow}</div>
            <div class="metric-label">🐌 Slow Queries</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col7:
        total_select_star = df["SELECT_STAR_QUERIES"].sum()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{total_select_star}</div>
            <div class="metric-label">⭐ SELECT * Queries</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col8:
        avg_failure_rate = df["FAILURE_CANCELLATION_RATE_PCT"].mean()
        st.markdown(
            f"""
        <div class="metric-card fade-in">
            <div class="metric-value">{avg_failure_rate:.1f}%</div>
            <div class="metric-label">❌ Avg Failure Rate</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    # Enhanced AgGrid Table
    st.markdown(
        '<div class="section-title fade-in">👥 User Analytics Table</div>',
        unsafe_allow_html=True,
    )
    st.markdown(
        '<p style="color: #666; margin-bottom: 20px;">Click on any user row to view detailed query analysis and recommendations</p>',
        unsafe_allow_html=True,
    )

    # Prepare data for AgGrid
    display_df = df.copy()

    # Format columns for better display
    display_df["TOTAL_CREDITS"] = display_df["TOTAL_CREDITS"].apply(
        lambda x: f"${x:,.2f}"
    )
    display_df["AVG_EXECUTION_TIME_MS"] = display_df["AVG_EXECUTION_TIME_MS"].apply(
        lambda x: f"{x:,.1f}ms"
    )
    display_df["TOTAL_DATA_SCANNED_GB"] = display_df["TOTAL_DATA_SCANNED_GB"].apply(
        lambda x: f"{x:,.2f}GB"
    )
    display_df["FAILURE_CANCELLATION_RATE_PCT"] = display_df[
        "FAILURE_CANCELLATION_RATE_PCT"
    ].apply(lambda x: f"{x:.2f}%")

    # Configure AgGrid
    gb = GridOptionsBuilder.from_dataframe(display_df)

    # Configure selection
    gb.configure_selection("single", use_checkbox=False, rowMultiSelectWithClick=False)

    # Configure columns
    gb.configure_column(
        "USER_NAME", headerName="👤 User Name", width=900, pinned="left"
    )
    gb.configure_column("TOTAL_QUERIES", headerName="🔍 Total Queries", width=120)
    gb.configure_column("TOTAL_CREDITS", headerName="💰 Credits", width=120)
    gb.configure_column(
        "WEIGHTED_SCORE",
        headerName="⭐ Score",
        width=100,
        type=["numericColumn", "numberColumnFilter"],
    )
    gb.configure_column("COST_STATUS", headerName="📊 Status", width=120)
    gb.configure_column("SPILLED_QUERIES", headerName="💾 Spilled", width=100)
    gb.configure_column("SLOW_QUERIES", headerName="🐌 Slow", width=100)
    gb.configure_column("SELECT_STAR_QUERIES", headerName="⭐ SELECT *", width=120)
    gb.configure_column("AVG_EXECUTION_TIME_MS", headerName="⏱️ Avg Time", width=120)
    gb.configure_column(
        "TOTAL_DATA_SCANNED_GB", headerName="📊 Data Scanned", width=140
    )
    gb.configure_column(
        "FAILURE_CANCELLATION_RATE_PCT", headerName="❌ Failure Rate", width=130
    )

    # Hide some columns to keep the table manageable
    columns_to_hide = [
        "OVER_PROVISIONED_QUERIES",
        # "PEAK_HOUR_LONG_RUNNING_QUERIES",
        # "UNPARTITIONED_SCAN_QUERIES",
        # "REPEATED_QUERIES",
        # "COMPLEX_JOIN_QUERIES",
        # "ZERO_RESULT_QUERIES",
        # "HIGH_COMPILE_QUERIES",
        # "UNTAGGED_QUERIES",
        # "UNLIMITED_ORDER_BY_QUERIES",
        # "LARGE_GROUP_BY_QUERIES",
        # "EXPENSIVE_DISTINCT_QUERIES",
        # "INEFFICIENT_LIKE_QUERIES",
        # "NO_RESULTS_WITH_SCAN_QUERIES",
        # "CARTESIAN_JOIN_QUERIES",
        # "HIGH_COMPILE_RATIO_QUERIES",
        "QUERY_SAMPLES",
        "RECOMMENDATIONS",
    ]

    for col in columns_to_hide:
        if col in display_df.columns:
            gb.configure_column(col, hide=True)

    # Configure grid options
    gb.configure_grid_options(
        domLayout="normal",
        enableRangeSelection=True,
        enableRowGrouping=True,
        enablePivot=True,
        enableValue=True,
        rowHeight=50,
        headerHeight=60,
    )

    grid_options = gb.build()

    # Display the grid
    grid_response = AgGrid(
        display_df,
        gridOptions=grid_options,
        data_return_mode=DataReturnMode.FILTERED_AND_SORTED,
        update_mode=GridUpdateMode.SELECTION_CHANGED,
        fit_columns_on_grid_load=False,
        theme="streamlit",
        height=500,
        width="100%",
        reload_data=False,
    )

    # Handle row selection
    if (
        grid_response["selected_rows"] is not None
        and len(grid_response["selected_rows"]) > 0
    ):
        selected_row = grid_response["selected_rows"]
        st.session_state.selected_user = selected_row["USER_NAME"]
        st.session_state.view_mode = "details"
        st.rerun()

    # Charts section
    st.markdown(
        '<div class="section-title fade-in">📊 Analytics Visualizations</div>',
        unsafe_allow_html=True,
    )

    col1, col2 = st.columns(2)

    with col1:
        fig_scatter = px.scatter(
            df,
            x="TOTAL_QUERIES",
            y="TOTAL_CREDITS",
            hover_data=["USER_NAME", "WEIGHTED_SCORE"],
            title="💰 Credits vs Total Queries",
            color="WEIGHTED_SCORE",
            size="TOTAL_DATA_SCANNED_GB",
            color_continuous_scale="Viridis",
        )
        fig_scatter.update_layout(
            height=500,
            title_font_size=16,
            plot_bgcolor="rgba(0,0,0,0)",
            paper_bgcolor="rgba(0,0,0,0)",
        )
        st.plotly_chart(fig_scatter, use_container_width=True)

    with col2:
        status_counts = df["COST_STATUS"].value_counts()
        fig_pie = px.pie(
            values=status_counts.values,
            names=status_counts.index,
            title="📊 Cost Status Distribution",
            color_discrete_sequence=px.colors.qualitative.Set3,
        )
        fig_pie.update_layout(
            height=500,
            title_font_size=16,
            plot_bgcolor="rgba(0,0,0,0)",
            paper_bgcolor="rgba(0,0,0,0)",
        )
        st.plotly_chart(fig_pie, use_container_width=True)


def show_details_view(df):
    """Display detailed view for selected user"""

    # Back button
    if st.button("← Back to Table", key="back_button"):
        st.session_state.view_mode = "table"
        st.session_state.selected_user = None
        st.session_state.selected_metric = None
        st.rerun()

    user_data = df[df["USER_NAME"] == st.session_state.selected_user]

    # User header
    st.markdown(
        f"""
    <div class="query-detail-container fade-in">
        <h1>🔍 Detailed Analysis for {st.session_state.selected_user}</h1>
        <div style="display: flex; justify-content: space-between; align-items: center; margin: 20px 0;">
            <div>
                <span style="font-size: 1.2em; color: #666;">Overall Performance Score:</span>
                <span style="font-size: 2em; font-weight: bold; color: #667eea; margin-left: 10px;">{user_data['WEIGHTED_SCORE']:.1f}</span>
            </div>
            <div>
                <span class="status-{'optimal' if user_data['COST_STATUS'] == 'Optimal' else 'warning' if user_data['COST_STATUS'] == 'Warning' else 'critical'}">
                    {user_data['COST_STATUS']}
                </span>
            </div>
        </div>
    </div>
    """,
        unsafe_allow_html=True,
    )

    # User metrics overview
    st.markdown(
        '<div class="section-title">📊 User Performance Overview</div>',
        unsafe_allow_html=True,
    )

    col1, col2, col3, col4 = st.columns(4)
    with col1:
        st.markdown(
            f"""
        <div class="metric-mini-card">
            <div style="font-size: 1.8em; font-weight: bold; color: #667eea;">{user_data['TOTAL_QUERIES']:,}</div>
            <div style="color: #666; font-size: 0.9em;">Total Queries</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col2:
        st.markdown(
            f"""
        <div class="metric-mini-card">
            <div style="font-size: 1.8em; font-weight: bold; color: #667eea;">${user_data['TOTAL_CREDITS']:,.2f}</div>
            <div style="color: #666; font-size: 0.9em;">Total Credits</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col3:
        st.markdown(
            f"""
        <div class="metric-mini-card">
            <div style="font-size: 1.8em; font-weight: bold; color: #667eea;">{user_data['AVG_EXECUTION_TIME_MS']:,.1f}ms</div>
            <div style="color: #666; font-size: 0.9em;">Avg Execution Time</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    with col4:
        st.markdown(
            f"""
        <div class="metric-mini-card">
            <div style="font-size: 1.8em; font-weight: bold; color: #667eea;">{user_data['TOTAL_DATA_SCANNED_GB']:,.2f}GB</div>
            <div style="color: #666; font-size: 0.9em;">Data Scanned</div>
        </div>
        """,
            unsafe_allow_html=True,
        )

    # Query type breakdown
    st.markdown(
        '<div class="section-title">🔍 Query Type Analysis</div>',
        unsafe_allow_html=True,
    )

    query_metrics = [
        ("SPILLED_QUERIES", "💾 Spilled Queries", "Queries that spilled to disk"),
        ("SLOW_QUERIES", "🐌 Slow Queries", "Queries with high execution time"),
        ("SELECT_STAR_QUERIES", "⭐ SELECT * Queries", "Queries using SELECT *"),
        (
            "COMPLEX_JOIN_QUERIES",
            "🔗 Complex Join Queries",
            "Queries with complex joins",
        ),
        ("REPEATED_QUERIES", "🔄 Repeated Queries", "Frequently repeated queries"),
        (
            "HIGH_COMPILE_QUERIES",
            "⚙️ High Compile Queries",
            "Queries with high compile time",
        ),
    ]

    cols = st.columns(3)
    for i, (metric, title, description) in enumerate(query_metrics):
        with cols[i % 3]:
            value = user_data.get(metric, 0)
            percentage = (
                (value / user_data["TOTAL_QUERIES"] * 100)
                if user_data["TOTAL_QUERIES"] > 0
                else 0
            )

            st.markdown(
                f"""
            <div class="query-card">
                <div style="display: flex; justify-content: space-between; align-items: center;">
                    <div>
                        <h4 style="margin: 0; color: #2c3e50;">{title}</h4>
                        <p style="margin: 5px 0; color: #666; font-size: 0.9em;">{description}</p>
                    </div>
                    <div style="text-align: right;">
                        <div style="font-size: 1.5em; font-weight: bold; color: #667eea;">{value}</div>
                        <div style="color: #666; font-size: 0.9em;">{percentage:.1f}%</div>
                    </div>
                </div>
            </div>
            """,
                unsafe_allow_html=True,
            )

    # Sample queries section
    if "QUERY_SAMPLES" in user_data and user_data["QUERY_SAMPLES"]:
        st.markdown(
            '<div class="section-title">📋 Sample Query Analysis</div>',
            unsafe_allow_html=True,
        )

        query_samples = user_data["QUERY_SAMPLES"]
        if isinstance(query_samples, str):
            try:
                query_samples = ast.literal_eval(query_samples)
            except Exception as e:
                st.error(f"Failed to parse query samples: {e}")
                query_samples = {}

        # Create tabs for different query types
        if query_samples:
            tabs = st.tabs(
                [key.replace("_", " ").title() for key in query_samples.keys()]
            )

            for i, (sample_key, samples) in enumerate(query_samples.items()):
                with tabs[i]:
                    for j, sample in enumerate(samples[:3]):  # Show top 3 samples
                        with st.expander(
                            f"Query {j+1}: {sample.get('query_id', 'N/A')}",
                            expanded=j == 0,
                        ):
                            st.code(
                                sample.get("query_text", "No query text available"),
                                language="sql",
                            )

                            # Metrics in columns
                            metric_cols = st.columns(4)
                            with metric_cols[0]:
                                execution_time = sample.get("execution_time_ms", 0)
                                color = (
                                    "#28a745" if execution_time < 1000 else "#dc3545"
                                )
                                st.markdown(
                                    f"**⏱️ Execution Time**<br><span style='color: {color}; font-size: 1.2em; font-weight: bold;'>{execution_time:,}ms</span>",
                                    unsafe_allow_html=True,
                                )

                            with metric_cols[1]:
                                st.markdown(
                                    f"**📊 Bytes Scanned**<br><span style='font-size: 1.2em; font-weight: bold;'>{sample.get('bytes_scanned', 0):,}</span>",
                                    unsafe_allow_html=True,
                                )

                            with metric_cols[2]:
                                st.markdown(
                                    f"**🏭 Warehouse**<br><span style='font-size: 1.2em; font-weight: bold;'>{sample.get('warehouse_size', 'N/A')}</span>",
                                    unsafe_allow_html=True,
                                )

                            with metric_cols[3]:
                                st.markdown(
                                    f"**📅 Start Time**<br><span style='font-size: 1.2em; font-weight: bold;'>{sample.get('start_time', 'N/A')}</span>",
                                    unsafe_allow_html=True,
                                )

                            # Spill information if available
                            if "bytes_spilled_to_local_storage" in sample:
                                st.markdown("**💾 Spill Information:**")
                                spill_cols = st.columns(2)
                                with spill_cols[0]:
                                    st.info(
                                        f"Local Spill: {sample.get('bytes_spilled_to_local_storage', 0):,} bytes"
                                    )
                                with spill_cols[1]:
                                    st.info(
                                        f"Remote Spill: {sample.get('bytes_spilled_to_remote_storage', 0):,} bytes"
                                    )

    # Recommendations section
    if "RECOMMENDATIONS" in user_data and user_data["RECOMMENDATIONS"]:
        st.markdown(
            '<div class="section-title">💡 Optimization Recommendations</div>',
            unsafe_allow_html=True,
        )

        recommendations = user_data["RECOMMENDATIONS"]
        if isinstance(recommendations, str):
            try:
                recommendations = ast.literal_eval(recommendations)
            except Exception as e:
                st.error(f"Failed to parse recommendations: {e}")
                recommendations = []

        for i, rec in enumerate(recommendations):
            st.markdown(
                f"""
            <div class="recommendation-card">
                <h4 style="margin: 0 0 10px 0; color: #856404;">💡 Recommendation {i+1}</h4>
                <p style="margin: 0; font-size: 1.1em; line-height: 1.6;">{rec}</p>
            </div>
            """,
                unsafe_allow_html=True,
            )


def show_empty_state():
    """Display empty state when no data is available"""
    st.markdown(
        """
    <div class="query-detail-container fade-in" style="text-align: center; padding: 50px;">
        <h2>📊 Welcome to Snowflake Analytics Dashboard</h2>
        <p style="font-size: 1.2em; color: #666; margin: 20px 0;">
            No data available. Please upload JSON data using the sidebar to get started!
        </p>
        <div style="background: #f8f9fa; padding: 30px; border-radius: 15px; margin: 30px 0; text-align: left; max-width: 600px; margin: 30px auto;">
            <h3>📋 Expected JSON Format:</h3>
            <p>Your JSON should contain user analytics data with the following structure:</p>
            <ul style="text-align: left; color: #666;">
                <li><strong>USER_NAME</strong> - Name of the user</li>
                <li><strong>TOTAL_QUERIES</strong> - Total number of queries executed</li>
                <li><strong>TOTAL_CREDITS</strong> - Total credits consumed</li>
                <li><strong>WEIGHTED_SCORE</strong> - Performance score</li>
                <li><strong>Various query metrics</strong> (SPILLED_QUERIES, SELECT_STAR_QUERIES, etc.)</li>
                <li><strong>QUERY_SAMPLES</strong> - Detailed query information with examples</li>
                <li><strong>RECOMMENDATIONS</strong> - Optimization suggestions</li>
            </ul>
        </div>
    </div>
    """,
        unsafe_allow_html=True,
    )


# Run the dashboard with sample data
if __name__ == "__main__":
    st.subheader("🔧 Filters")
    col1, col2 = st.columns(2)

    with col1:
        date_filter, custom_start, custom_end = CommonUI.render_date_filter()

    with col2:
        selected_user = CommonUI.render_object_filter("user", query_executor)

    # === PREPARE QUERY PARAMETERS ===
    # Get date range
    start_date, end_date = query_executor.get_date_range(
        date_filter, custom_start, custom_end
    )

    # Build object filter
    object_filter = QueryBuilder.build_object_filter("user", selected_user)
    print(f"this is the meeee {object_filter}")
    st.markdown("---")
    query_key = "user_all_quries"
    df = {}
    query = QueryBuilder.prepare_query(
        USER_360_QUERIES, query_key, start_date, end_date, object_filter
    )

    if not query:
        st.error(f"Failed to prepare query for {query_key}")
        # continue

    # Execute query
    try:
        data = query_executor._execute_single_query(
            query, {}, query_key  # Already formatted
        )
        df = data
    except Exception as e:
        st.error(f"Query execution failed for {query_key}: {str(e)}")

    # Analysis type selection
    s = len(df)
    # sample_data = load_sample_data()
    # df = pd.DataFrame(sample_data)

    create_snowflake_dashboard(df)

new22

Armaan Khan — Wed, 30 Jul 2025 05:45:56 +0000

import json
from datetime import datetime

import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import streamlit as st

from core.query_executor import query_executor
from queries.QueryBuilder import QueryBuilder
from queries.filter import CommonUI
from queries.final_last import USER_360_QUERIES


def create_snowflake_dashboard(df):
    # Page configuration
    # st.set_page_config(
    #     page_title="Snowflake Query Analytics",
    #     page_icon="❄️",
    #     layout="wide",
    #     initial_sidebar_state="expanded",
    # )

    # Custom CSS for better styling
    st.markdown(
        """
    <style>
        .main-header {
            background: linear-gradient(90deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 20px;
            border-radius: 10px;
            text-align: center;
            margin-bottom: 20px;
            box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
        }

        .metric-card {
            background: white;
            padding: 20px;
            border-radius: 10px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            border-left: 4px solid #667eea;
            margin: 10px 0;
            transition: transform 0.2s;
        }

        .metric-card:hover {
            transform: translateY(-2px);
            box-shadow: 0 4px 8px rgba(0,0,0,0.15);
        }

        .metric-value {
            font-size: 2em;
            font-weight: bold;
            color: #2c3e50;
        }

        .metric-label {
            color: #7f8c8d;
            font-size: 0.9em;
            margin-top: 5px;
        }

        .status-normal {
            background: #d4edda;
            color: #155724;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .status-warning {
            background: #fff3cd;
            color: #856404;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .status-critical {
            background: #f8d7da;
            color: #721c24;
            padding: 5px 10px;
            border-radius: 20px;
            font-weight: bold;
        }

        .query-detail-card {
            background: #f8f9fa;
            padding: 15px;
            border-radius: 8px;
            margin: 10px 0;
            border-left: 4px solid #007bff;
        }

        .recommendation-card {
            background: #fff3cd;
            padding: 15px;
            border-radius: 8px;
            margin: 10px 0;
            border-left: 4px solid #ffc107;
        }

        .table-container {
            overflow-x: auto;
            background: white;
            padding: 20px;
            border-radius: 12px;
            box-shadow: 0 4px 8px rgba(0,0,0,0.1);
            margin-top: 20px;
            max-height: 600px;
            overflow-y: auto;
            scrollbar-width: thin;
            scrollbar-color: #667eea #f1f3f5;
        }

        .table-container::-webkit-scrollbar {
            height: 12px;
            width: 8px;
        }

        .table-container::-webkit-scrollbar-track {
            background: #f1f3f5;
            border-radius: 10px;
        }

        .table-container::-webkit-scrollbar-thumb {
            background: #667eea;
            border-radius: 10px;
        }

        .table-row {
            display: flex;
            align-items: center;
            padding: 12px ,20px ;
            border-bottom: 1px solid #e9ecef;
            transition: background-color 0.2s;
        }

        .table-row:nth-child(even) {
            background-color: #f8f9fa;
        }



        .table-cell {
            flex: 1;
            text-align: center;
            padding: 10px;
            font-size: 0.95em;
            color: #2c3e50;
        }

        .table-header {
            font-weight: 600;
            background: #667eea;
            color: white;
            padding: 12px;
            border-radius: 8px 8px 0 0;
            position: sticky;
            top: 0;
            z-index: 1;
        }

        .user-cell {
            flex: 2.5;
            text-align: left;
            font-weight: bold;
        }

        .status-cell {
            flex: 1.2;
        }

        .score-cell {
            flex: 1.2;
        }

        .stButton>button {
            border: none;
            background: none;
            color: #007bff;
            cursor: pointer;
            font-size: 0.95em;
            padding: 5px;
            width: 100%;
            text-align: center;
        }

        .stButton>button:hover {
            color: #ffffff
            background: #e3f2fd;
            border-radius: 4px;
        }
    </style>
    """,
        unsafe_allow_html=True,
    )

    # Initialize session state
    if "selected_user" not in st.session_state:
        st.session_state.selected_user = None
    if "selected_metric" not in st.session_state:
        st.session_state.selected_metric = None

    # Header
    st.markdown(
        """
    <div class="main-header">
        <h1>❄️ Snowflake Query Analytics Dashboard</h1>
        <p>Monitor and optimize your Snowflake query performance</p>
    </div>
    """,
        unsafe_allow_html=True,
    )

    # Sidebar for data input
    with st.sidebar:
        st.header("📊 Data Management")
        uploaded_file = st.file_uploader("Upload JSON Data", type=["json"])
        if uploaded_file is not None:
            try:
                new_data = json.load(uploaded_file)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                    st.success("Data loaded successfully!")
                else:
                    df = pd.DataFrame([new_data])
                    st.success("Data loaded successfully!")
            except Exception as e:
                st.error(f"Error loading data: {e}")

        st.subheader("Or paste JSON data:")
        json_input = st.text_area(
            "JSON Data", height=200, placeholder="Paste your JSON data here..."
        )
        if st.button("Load JSON Data"):
            try:
                new_data = json.loads(json_input)
                if isinstance(new_data, list):
                    df = pd.DataFrame(new_data)
                else:
                    df = pd.DataFrame([new_data])
                st.success("Data loaded successfully!")
                st.rerun()
            except Exception as e:
                st.error(f"Error parsing JSON: {e}")

    # Main dashboard
    if not df.empty:
        # Overall metrics cards
        st.header("📈 Overall Metrics")
        col1, col2, col3, col4 = st.columns(4)

        with col1:
            total_users = len(df)
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_users}</div>
                <div class="metric-label">Total Users</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col2:
            total_queries = df["TOTAL_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_queries:,}</div>
                <div class="metric-label">Total Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col3:
            total_credits = df["TOTAL_CREDITS"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">${total_credits:.2f}</div>
                <div class="metric-label">Total Credits</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col4:
            avg_score = df["WEIGHTED_SCORE"].mean()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{avg_score:.1f}</div>
                <div class="metric-label">Avg Weighted Score</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        col5, col6, col7, col8 = st.columns(4)

        with col5:
            total_spilled = df["SPILLED_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_spilled}</div>
                <div class="metric-label">Total Spilled Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col6:
            total_slow = df["SLOW_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_slow}</div>
                <div class="metric-label">Total Slow Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col7:
            total_select_star = df["SELECT_STAR_QUERIES"].sum()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{total_select_star}</div>
                <div class="metric-label">SELECT * Queries</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        with col8:
            avg_failure_rate = df["FAILURE_CANCELLATION_RATE_PCT"].mean()
            st.markdown(
                f"""
            <div class="metric-card">
                <div class="metric-value">{avg_failure_rate:.1f}%</div>
                <div class="metric-label">Avg Failure Rate</div>
            </div>
            """,
                unsafe_allow_html=True,
            )

        st.markdown("---")

        # Query Details Container


        import ast

        # Custom CSS for modern styling
        st.markdown(
            """
            <style>
            .query-detail-card, .recommendation-card {
                background: #ffffff;
                border-radius: 12px;
                padding: 20px;
                box-shadow: 0 4px 12px rgba(0, 0, 0, 0.1);
                margin-bottom: 20px;
                transition: transform 0.2s ease-in-out;
            }
            .query-detail-card:hover, .recommendation-card:hover {
                transform: translateY(-5px);
            }
            .metric-card {
                background: #f8f9fa;
                border-radius: 8px;
                padding: 10px;
                text-align: center;
            }
            .stButton > button {
                background: #0066cc;
                color: white;
                border-radius: 8px;
                padding: 10px 20px;
                border: none;
                transition: background 0.3s;
            }
            .stButton > button:hover {
                background: #0052a3;
            }
            .st-expander {
                border-radius: 8px;
                border: 1px solid #e0e0e0;
            }
            .header-container {
                position: sticky;
                top: 0;
                background: #f8f9fa;
                z-index: 100;
                padding: 10px 0;
            }
            h4 {
                font-size: 1.5rem;
                color: #333;
                margin-bottom: 10px;
            }
            .metric-label {
                font-weight: bold;
                color: #555;
            }
            .status-green {
                color: #28a745;
            }
            .status-red {
                color: #dc3545;
            }
            </style>
            """,
            unsafe_allow_html=True,
        )

        def normalize_metric_name(metric_name):
            return (
                metric_name.lower()
                .replace("_queries", "")
                .replace("_", " ")
                .replace(" ", "_")
            )

        # Main container
        query_container = st.container()
        with query_container:
            if st.session_state.get("selected_user") and st.session_state.get("selected_metric"):
                # Sticky header
                with st.container():
                    st.markdown(
                        f"""
                        <div class="header-container">
                            <h2>🔍 Query Details for {st.session_state.selected_user}</h2>
                            <h4>Metric: {st.session_state.selected_metric.replace('_', ' ').title()}</h4>
                        </div>
                        """,
                        unsafe_allow_html=True,
                    )

                user_data = df[df["USER_NAME"] == st.session_state.selected_user].iloc[0]
                metric_value = user_data[st.session_state.selected_metric]

                # Layout: 2-column grid
                col1, col2 = st.columns([3, 1])

                with col1:
                    st.markdown(
                        f"""
                        <div class="query-detail-card" role="region" aria-label="Query Details">
                            <h4>{st.session_state.selected_metric.replace('_', ' ').title()}: {metric_value}</h4>
                            <p><span class="metric-label">User:</span> {st.session_state.selected_user}</p>
                            <p><span class="metric-label">Total Queries:</span> {user_data['TOTAL_QUERIES']}</p>
                            <p><span class="metric-label">Weighted Score:</span> {user_data['WEIGHTED_SCORE']}</p>
                            <p><span class="metric-label">Cost Status:</span> 
                                <span class="{ 'status-green' if user_data['COST_STATUS'] == 'Optimal' else 'status-red' }">
                                    {user_data['COST_STATUS']}
                                </span>
                            </p>
                        </div>
                        """,
                        unsafe_allow_html=True,
                    )

                with col2:
                    if st.button("❌ Clear", help="Clear current selection"):
                        st.session_state.selected_user = None
                        st.session_state.selected_metric = None
                        st.rerun()

                # Sample Queries Section
                if "QUERY_SAMPLES" in user_data and user_data["QUERY_SAMPLES"]:
                    st.subheader("📋 Sample Queries")

                    query_samples = user_data["QUERY_SAMPLES"]
                    if isinstance(query_samples, str):
                        with st.spinner("Parsing query samples..."):
                            try:
                                query_samples = ast.literal_eval(query_samples)
                            except Exception as e:
                                st.error(f"Failed to parse QUERY_SAMPLES: {e}")
                                query_samples = {}

                    sample_key = normalize_metric_name(st.session_state.selected_metric)

                    if sample_key in query_samples:
                        samples = query_samples[sample_key]
                        for i, sample in enumerate(samples):
                            with st.expander(f"Query {i+1}: {sample.get('query_id', 'N/A')}", expanded=False):
                                st.code(
                                    sample.get("query_text", "No query text available"),
                                    language="sql",
                                )

                                # Metrics in a grid layout
                                col1, col2, col3 = st.columns(3)
                                with col1:
                                    execution_time = sample.get("execution_time_ms", 0)
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Execution Time</p>
                                            <p class="{'status-green' if execution_time < 1000 else 'status-red'}">
                                                {execution_time:,}ms
                                            </p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )
                                with col2:
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Bytes Scanned</p>
                                            <p>{sample.get('bytes_scanned', 0):,}</p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )
                                with col3:
                                    st.markdown(
                                        f"""
                                        <div class="metric-card">
                                            <p class="metric-label">Warehouse Size</p>
                                            <p>{sample.get('warehouse_size', 'N/A')}</p>
                                        </div>
                                        """,
                                        unsafe_allow_html=True,
                                    )

                                if "bytes_spilled_to_local_storage" in sample:
                                    col4, col5 = st.columns(2)
                                    with col4:
                                        st.markdown(
                                            f"""
                                            <div class="metric-card">
                                                <p class="metric-label">Local Spill</p>
                                                <p>{sample.get('bytes_spilled_to_local_storage', 0):,} bytes</p>
                                            </div>
                                            """,
                                            unsafe_allow_html=True,
                                        )
                                    with col5:
                                        st.markdown(
                                            f"""
                                            <div class="metric-card">
                                                <p class="metric-label">Remote Spill</p>
                                                <p>{sample.get('bytes_spilled_to_remote_storage', 0):,} bytes</p>
                                            </div>
                                            """,
                                            unsafe_allow_html=True,
                                        )

                                st.info(f"Started at: {sample.get('start_time', 'N/A')}")
                    else:
                        st.info("No sample queries available for this metric.")

                # Recommendations Section
                if "RECOMMENDATIONS" in user_data and user_data["RECOMMENDATIONS"]:
                    st.subheader("💡 Recommendations")
                    recommendations = user_data["RECOMMENDATIONS"]
                    if isinstance(recommendations, str):
                        with st.spinner("Parsing recommendations..."):
                            try:
                                recommendations = ast.literal_eval(recommendations)
                            except Exception as e:
                                st.error(f"Failed to parse recommendations: {e}")
                                recommendations = []

                    for rec in recommendations:
                        st.markdown(
                            f"""
                            <div class="recommendation-card" role="region" aria-label="Recommendation">
                                <strong>💡 {rec}</strong>
                            </div>
                            """,
                            unsafe_allow_html=True,
                        )









        # Interactive table
        st.markdown("---")
        st.header("👥 User Analytics Table")
        st.markdown("*Click on any metric cell to view detailed query information*")

        display_df = df.copy()
        display_df["TOTAL_CREDITS"] = display_df["TOTAL_CREDITS"].apply(
            lambda x: f"${x:.2f}"
        )
        display_df["AVG_EXECUTION_TIME_MS"] = display_df["AVG_EXECUTION_TIME_MS"].apply(
            lambda x: f"{x:.1f}ms"
        )
        display_df["TOTAL_DATA_SCANNED_GB"] = display_df["TOTAL_DATA_SCANNED_GB"].apply(
            lambda x: f"{x:.2f}GB"
        )
        display_df["FAILURE_CANCELLATION_RATE_PCT"] = display_df[
            "FAILURE_CANCELLATION_RATE_PCT"
        ].apply(lambda x: f"{x:.2f}%")

        clickable_columns = [
            "SPILLED_QUERIES",
            "OVER_PROVISIONED_QUERIES",
            "PEAK_HOUR_LONG_RUNNING_QUERIES",
            "SELECT_STAR_QUERIES",
            "UNPARTITIONED_SCAN_QUERIES",
            "REPEATED_QUERIES",
            "COMPLEX_JOIN_QUERIES",
            "ZERO_RESULT_QUERIES",
            "HIGH_COMPILE_QUERIES",
            "UNTAGGED_QUERIES",
            "UNLIMITED_ORDER_BY_QUERIES",
            "LARGE_GROUP_BY_QUERIES",
            "SLOW_QUERIES",
            "EXPENSIVE_DISTINCT_QUERIES",
            "INEFFICIENT_LIKE_QUERIES",
            "NO_RESULTS_WITH_SCAN_QUERIES",
            "CARTESIAN_JOIN_QUERIES",
            "HIGH_COMPILE_RATIO_QUERIES",
        ]

        # Table container
        with st.container():
            st.markdown('<div class="table-container">', unsafe_allow_html=True)

            # Header row
            st.markdown('<div class="table-header table-row">', unsafe_allow_html=True)
            header_cols = st.columns([2.5] + [1] * len(clickable_columns) + [1.2, 1.2])
            with header_cols[0]:
                st.markdown(
                    '<div class="table-cell user-cell">User Name</div>',
                    unsafe_allow_html=True,
                )
            for i, col in enumerate(clickable_columns):
                with header_cols[i + 1]:
                    st.markdown(
                        f'<div class="table-cell">{col.replace("_", " ").title()}</div>',
                        unsafe_allow_html=True,
                    )
            with header_cols[-2]:
                st.markdown(
                    '<div class="table-cell score-cell">Weighted Score</div>',
                    unsafe_allow_html=True,
                )
            with header_cols[-1]:
                st.markdown(
                    '<div class="table-cell status-cell">Cost Status</div>',
                    unsafe_allow_html=True,
                )
            st.markdown("</div>", unsafe_allow_html=True)

            # Data rows
            for idx, row in display_df.iterrows():
                st.markdown('<div class="table-row">', unsafe_allow_html=True)
                row_cols = st.columns([2.5] + [1] * len(clickable_columns) + [1.2, 1.2])

                with row_cols[0]:
                    st.markdown(
                        f'<div class="table-cell user-cell">{row["USER_NAME"]}</div>',
                        unsafe_allow_html=True,
                    )

                for i, col in enumerate(clickable_columns):
                    with row_cols[i + 1]:
                        if st.button(f"{row[col]}", key=f"{idx}_{col}"):
                            st.session_state.selected_user = row["USER_NAME"]
                            st.session_state.selected_metric = col
                            # Scroll to query details
                            st.markdown(
                                """
                                <script>
                                    document.querySelector('.query-detail-card').scrollIntoView({ 
                                        behavior: 'smooth', 
                                        block: 'start' 
                                    });
                                </script>
                                """,
                                unsafe_allow_html=True,
                            )
                            st.rerun()

                with row_cols[-2]:
                    st.markdown(
                        f'<div class="table-cell score-cell">{row["WEIGHTED_SCORE"]:.1f}</div>',
                        unsafe_allow_html=True,
                    )

                with row_cols[-1]:
                    status = row["COST_STATUS"]
                    color_class = {
                        "Normal": "status-normal",
                        "Warning": "status-warning",
                        "Critical": "status-critical",
                    }.get(status, "status-normal")
                    st.markdown(
                        f'<div class="table-cell status-cell"><span class="{color_class}">{status}</span></div>',
                        unsafe_allow_html=True,
                    )
                st.markdown("</div>", unsafe_allow_html=True)

            st.markdown("</div>", unsafe_allow_html=True)

        # Charts section
        st.markdown("---")
        st.header("📊 Analytics Charts")

        col1, col2 = st.columns(2)

        with col1:
            fig_scatter = px.scatter(
                df,
                x="TOTAL_QUERIES",
                y="TOTAL_CREDITS",
                hover_data=["USER_NAME", "WEIGHTED_SCORE"],
                title="Credits vs Total Queries",
                color="WEIGHTED_SCORE",
                size="TOTAL_DATA_SCANNED_GB",
            )
            fig_scatter.update_layout(height=400)
            st.plotly_chart(fig_scatter, use_container_width=True)

        with col2:
            status_counts = df["COST_STATUS"].value_counts()
            fig_pie = px.pie(
                values=status_counts.values,
                names=status_counts.index,
                title="Cost Status Distribution",
            )
            fig_pie.update_layout(height=400)
            st.plotly_chart(fig_pie, use_container_width=True)

    else:
        st.info(
            "No data available. Please upload JSON data using the sidebar to get started!"
        )
        st.markdown(
            """
        ### Expected JSON Format:
        Your JSON should contain user analytics data with the following structure:
        - USER_NAME
        - TOTAL_QUERIES
        - Various query metrics (SPILLED_QUERIES, SELECT_STAR_QUERIES, etc.)
        - QUERY_SAMPLES with detailed query information
        - RECOMMENDATIONS
        """
        )


# Sample data for testing

# Run the dashboard with sample data
if __name__ == "__main__":

    st.subheader("🔧 Filters")
    col1, col2 = st.columns(2)

    with col1:
        date_filter, custom_start, custom_end = CommonUI.render_date_filter()

    with col2:
        selected_user = CommonUI.render_object_filter("user", query_executor)

    # === PREPARE QUERY PARAMETERS ===
    # Get date range
    start_date, end_date = query_executor.get_date_range(
        date_filter, custom_start, custom_end
    )

    # Build object filter
    object_filter = QueryBuilder.build_object_filter("user", selected_user)
    print(f"this is the meeee {object_filter}")
    st.markdown("---")
    query_key = "user_all_quries"
    df = {}
    query = QueryBuilder.prepare_query(
        USER_360_QUERIES, query_key, start_date, end_date, object_filter
    )

    if not query:
        st.error(f"Failed to prepare query for {query_key}")
        # continue

    # Execute query
    try:
        data = query_executor._execute_single_query(
            query, {}, query_key  # Already formatted
        )
        df = data
    except Exception as e:
        st.error(f"Query execution failed for {query_key}: {str(e)}")

    # Analysis type selection
    s = len(df)
    # sample_data = load_sample_data()
    # df = pd.DataFrame(sample_data)


    create_snowflake_dashboard(df)

chart

Armaan Khan — Wed, 23 Jul 2025 06:18:06 +0000

chart
from typing import Dict

import numpy as np
import pandas as pd
import plotly.express as px
import plotly.graph_objects as go
import streamlit as st
from plotly.subplots import make_subplots

class ChartBuilder:
    """Builds charts and KPIs with error handling and fallback to tables"""

    @staticmethod
    def create_kpi_card(
        value, title: str, format_type: str = "number", color: str = "#1f77b4"
    ):
        """Creates a KPI metric card"""
        try:
            if pd.isna(value) or value is None:
                formatted_value = "N/A"
            else:
                if format_type == "currency":
                    formatted_value = f"${float(value):,.2f}"
                elif format_type == "percentage":
                    formatted_value = f"{float(value):.1f}%"
                else:
                    formatted_value = f"{float(value):,.0f}"

            # Using Streamlit's built-in metric
            st.metric(label=title, value=formatted_value)

        except Exception as e:
            st.error(f"Error creating KPI card: {str(e)}")
            st.metric(label=title, value="Error")

    @staticmethod
    def create_chart(df: pd.DataFrame, config: Dict) -> None:
        """
        Creates charts based on config with fallback to table on errors
        """
        if df.empty:
            st.warning("No data available for chart")
            return

        chart_type = config.get("chart_type", "table").lower()
        title = config.get("title", "Chart")

        try:
            if chart_type == "kpi":
                # Handle KPI from dataframe
                value_col = config.get("value_column")
                if value_col and value_col in df.columns:
                    value = df[value_col].iloc[0] if len(df) > 0 else 0
                    ChartBuilder.create_kpi_card(
                        value,
                        title,
                        config.get("format", "number"),
                        config.get("color", "#1f77b4"),
                    )
                else:
                    st.error("KPI value column not found")
                return

            elif chart_type == "bar":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for bar chart")

                fig = px.bar(
                    df,
                    x=x_col,
                    y=y_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                fig.update_layout(showlegend=False)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "line":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for line chart")

                fig = px.line(
                    df,
                    x=x_col,
                    y=y_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "pie":
                y_cols = config.get("y_axis")  # This is a list of metric columns
                stack_field = config.get("stack_field", "METRIC_TYPE")
                title = config.get("title", "Pie Chart")

                if not y_cols or not all(col in df.columns for col in y_cols):
                    raise ValueError("Required columns not found for pie chart")

                # Melt and aggregate
                df_melted = df.melt(
                    id_vars=["USER_NAME"] if "USER_NAME" in df.columns else ["user"],
                    value_vars=y_cols,
                    var_name=stack_field,
                    value_name="Value"
                )
                df_summary = df_melted.groupby(stack_field, as_index=False)["Value"].sum()
                df_summary.rename(columns={"Value": "TOTAL_COUNT"}, inplace=True)

                # Render pie chart
                fig = px.pie(df_summary, names=stack_field, values="TOTAL_COUNT", title=title)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "scatter":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for scatter chart")

                fig = px.scatter(
                    df,
                    x=x_col,
                    y=y_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "area":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for area chart")

                fig = px.area(
                    df,
                    x=x_col,
                    y=y_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "histogram":
                x_col = config.get("x_axis")

                if not x_col or x_col not in df.columns:
                    raise ValueError("Required column not found for histogram")

                fig = px.histogram(
                    df,
                    x=x_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "box":
                y_col = config.get("y_axis")
                x_col = config.get("x_axis")  # optional for grouping

                if not y_col or y_col not in df.columns:
                    raise ValueError("Required column not found for box plot")

                fig = px.box(
                    df,
                    y=y_col,
                    x=x_col if x_col and x_col in df.columns else None,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "violin":
                y_col = config.get("y_axis")
                x_col = config.get("x_axis")  # optional for grouping

                if not y_col or y_col not in df.columns:
                    raise ValueError("Required column not found for violin plot")

                fig = px.violin(
                    df,
                    y=y_col,
                    x=x_col if x_col and x_col in df.columns else None,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "heatmap":
                # For heatmap, we'll use all numeric columns or specified columns
                numeric_cols = df.select_dtypes(include=[np.number]).columns
                if len(numeric_cols) < 2:
                    raise ValueError("Not enough numeric columns for heatmap")

                correlation_matrix = df[numeric_cols].corr()
                fig = px.imshow(
                    correlation_matrix, title=title, color_continuous_scale="RdBu_r"
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "donut":
                names_col = config.get("names_column", config.get("x_axis"))
                values_col = config.get("values_column", config.get("y_axis"))

                if (
                    not names_col
                    or not values_col
                    or names_col not in df.columns
                    or values_col not in df.columns
                ):
                    raise ValueError("Required columns not found for donut chart")

                fig = px.pie(
                    df,
                    names=names_col,
                    values=values_col,
                    title=title,
                    hole=0.4,  # This makes it a donut chart
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "funnel":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for funnel chart")

                fig = px.funnel(
                    df,
                    x=y_col,
                    y=x_col,
                    title=title,
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "gauge":
                value_col = config.get("value_column", config.get("y_axis"))
                if not value_col or value_col not in df.columns:
                    raise ValueError("Required column not found for gauge chart")

                value = df[value_col].iloc[0] if len(df) > 0 else 0
                max_val = config.get("max_value", df[value_col].max())

                fig = go.Figure(
                    go.Indicator(
                        mode="gauge+number",
                        value=value,
                        domain={"x": [0, 1], "y": [0, 1]},
                        title={"text": title},
                        gauge={
                            "axis": {"range": [None, max_val]},
                            "bar": {"color": config.get("color", "#1f77b4")},
                            "steps": [
                                {"range": [0, max_val / 2], "color": "lightgray"},
                                {"range": [max_val / 2, max_val], "color": "gray"},
                            ],
                            "threshold": {
                                "line": {"color": "red", "width": 4},
                                "thickness": 0.75,
                                "value": max_val * 0.9,
                            },
                        },
                    )
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "treemap":
                path_col = config.get("path_column", config.get("x_axis"))
                values_col = config.get("values_column", config.get("y_axis"))

                if (
                    not path_col
                    or not values_col
                    or path_col not in df.columns
                    or values_col not in df.columns
                ):
                    raise ValueError("Required columns not found for treemap")

                fig = px.treemap(df, path=[path_col], values=values_col, title=title)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "sunburst":
                path_col = config.get("path_column", config.get("x_axis"))
                values_col = config.get("values_column", config.get("y_axis"))

                if (
                    not path_col
                    or not values_col
                    or path_col not in df.columns
                    or values_col not in df.columns
                ):
                    raise ValueError("Required columns not found for sunburst")

                fig = px.sunburst(df, path=[path_col], values=values_col, title=title)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "waterfall":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for waterfall chart")

                fig = go.Figure(
                    go.Waterfall(
                        name=title,
                        orientation="v",
                        measure=["relative"] * len(df),
                        x=df[x_col],
                        textposition="outside",
                        text=[f"{v:,.0f}" for v in df[y_col]],
                        y=df[y_col],
                        connector={"line": {"color": "rgb(63, 63, 63)"}},
                    )
                )
                fig.update_layout(title=title, showlegend=False)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "stacked_bar":
                x_col = config.get("x_axis")
                y_cols = config.get("y_axis")  # This is a list
                stack_field = config.get("stack_field", "Metric Type")
                title = config.get("title", "Stacked Bar Chart")

                # Validate columns
                if (
                    not x_col
                    or not y_cols
                    or x_col not in df.columns
                    or not all(col in df.columns for col in y_cols)
                ):
                    raise ValueError("Required columns not found for stacked bar chart")

                # Melt the DataFrame to long format
                df_melted = df.melt(
                    id_vars=[x_col],
                    value_vars=y_cols,
                    var_name=stack_field,
                    value_name="Value",
                )

                # Create the chart
                fig = px.bar(
                    df_melted,
                    x=x_col,
                    y="Value",
                    color=stack_field,
                    title=title,
                    barmode="stack",
                    color_discrete_map=config.get(
                        "colors", {}
                    ),  # Optional custom colors
                )

                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "grouped_bar":
                x_col = config.get("x_axis")
                color_col = config.get("color_column")
                y_col = config.get("y_axis")
                stack_field = config.get("stack_field", "Metric Type")

                # if (
                #     not x_col
                #     or not y_col
                #     or x_col not in df.columns
                #    or not all(col in df.columns for col in y_col)
                # ):
                #     raise ValueError("Required columns not found for grouped bar chart")

                df_melted = df.melt(
                    id_vars=[x_col],
                    value_vars=y_col,
                    var_name=stack_field,
                    value_name="Value",
                )

                fig = px.bar(
                    df_melted,
                    x=x_col,
                      y="Value",
                    color=stack_field,
                    title=title,
                    barmode="group",
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "horizontal_bar":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError(
                        "Required columns not found for horizontal bar chart"
                    )

                fig = px.bar(
                    df,
                    x=y_col,
                    y=x_col,
                    title=title,
                    orientation="h",
                    color_discrete_sequence=[config.get("color", "#1f77b4")],
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "multi_line":
                x_col = config.get("x_axis")
                y_cols = config.get("y_columns", [])  # List of columns

                if not x_col or not y_cols or x_col not in df.columns:
                    raise ValueError("Required columns not found for multi-line chart")

                fig = go.Figure()
                colors = [
                    "#1f77b4",
                    "#ff7f0e",
                    "#2ca02c",
                    "#d62728",
                    "#9467bd",
                    "#8c564b",
                ]

                for i, y_col in enumerate(y_cols):
                    if y_col in df.columns:
                        fig.add_trace(
                            go.Scatter(
                                x=df[x_col],
                                y=df[y_col],
                                mode="lines+markers",
                                name=y_col,
                                line=dict(color=colors[i % len(colors)]),
                            )
                        )

                fig.update_layout(title=title, xaxis_title=x_col, yaxis_title="Values")
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "combo":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")
                line_col = config.get("line_column")

                if not all([x_col, y_col, line_col]) or not all(
                    [col in df.columns for col in [x_col, y_col, line_col]]
                ):
                    raise ValueError("Required columns not found for combo chart")

                fig = make_subplots(specs=[[{"secondary_y": True}]])

                # Add bar chart
                fig.add_trace(
                    go.Bar(x=df[x_col], y=df[y_col], name=y_col),
                    secondary_y=False,
                )

                # Add line chart
                fig.add_trace(
                    go.Scatter(
                        x=df[x_col], y=df[line_col], mode="lines+markers", name=line_col
                    ),
                    secondary_y=True,
                )

                fig.update_xaxes(title_text=x_col)
                fig.update_yaxes(title_text=y_col, secondary_y=False)
                fig.update_yaxes(title_text=line_col, secondary_y=True)
                fig.update_layout(title_text=title)

                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "bullet":
                value_col = config.get("value_column", config.get("y_axis"))
                target_col = config.get("target_column")

                if not value_col or value_col not in df.columns:
                    raise ValueError("Required column not found for bullet chart")

                value = df[value_col].iloc[0] if len(df) > 0 else 0
                target = (
                    df[target_col].iloc[0]
                    if target_col and target_col in df.columns
                    else value * 1.2
                )

                fig = go.Figure()

                fig.add_trace(
                    go.Indicator(
                        mode="number+gauge+delta",
                        value=value,
                        domain={"x": [0, 1], "y": [0, 1]},
                        title={"text": title},
                        delta={"reference": target},
                        gauge={
                            "shape": "bullet",
                            "axis": {"range": [None, target * 1.5]},
                            "threshold": {
                                "line": {"color": "red", "width": 2},
                                "thickness": 0.75,
                                "value": target,
                            },
                            "steps": [
                                {"range": [0, target * 0.5], "color": "lightgray"},
                                {"range": [target * 0.5, target], "color": "gray"},
                            ],
                            "bar": {"color": config.get("color", "#1f77b4")},
                        },
                    )
                )

                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "density_heatmap":
                x_col = config.get("x_axis")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError("Required columns not found for density heatmap")

                fig = px.density_heatmap(
                    df,
                    x=x_col,
                    y=y_col,
                    title=title,
                    marginal_x="histogram",
                    marginal_y="histogram",
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "stacked_area":
                x_col = config.get("x_axis")
                color_col = config.get("color_column")
                y_col = config.get("y_axis")

                if (
                    not x_col
                    or not y_col
                    or x_col not in df.columns
                    or y_col not in df.columns
                ):
                    raise ValueError(
                        "Required columns not found for stacked area chart"
                    )

                fig = px.area(
                    df,
                    x=x_col,
                    y=y_col,
                    color=color_col if color_col and color_col in df.columns else None,
                    title=title,
                )
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "radar":
                # For radar charts, we need multiple numeric columns
                categories_col = config.get("categories_column", config.get("x_axis"))
                values_cols = config.get("values_columns", [])

                if not categories_col or categories_col not in df.columns:
                    # Use numeric columns as categories if no specific column provided
                    numeric_cols = df.select_dtypes(
                        include=[np.number]
                    ).columns.tolist()
                    if len(numeric_cols) < 3:
                        raise ValueError("Not enough data for radar chart")

                    # Create radar chart from first row of numeric data
                    values = df[numeric_cols].iloc[0].values
                    categories = numeric_cols
                else:
                    # Use specified columns
                    if not values_cols:
                        values_cols = [
                            col
                            for col in df.columns
                            if col != categories_col
                            and pd.api.types.is_numeric_dtype(df[col])
                        ]

                    categories = df[categories_col].tolist()
                    values = df[values_cols[0]].tolist() if values_cols else []

                fig = go.Figure()

                fig.add_trace(
                    go.Scatterpolar(
                        r=values, theta=categories, fill="toself", name=title
                    )
                )

                fig.update_layout(
                    polar=dict(
                        radialaxis=dict(
                            visible=True,
                        )
                    ),
                    title=title,
                    showlegend=True,
                )

                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "candlestick":
                # Requires OHLC data
                date_col = config.get("date_column", config.get("x_axis"))
                open_col = config.get("open_column", "open")
                high_col = config.get("high_column", "high")
                low_col = config.get("low_column", "low")
                close_col = config.get("close_column", "close")

                required_cols = [date_col, open_col, high_col, low_col, close_col]
                if not all(col and col in df.columns for col in required_cols):
                    raise ValueError(
                        "Required OHLC columns not found for candlestick chart"
                    )

                fig = go.Figure(
                    data=go.Candlestick(
                        x=df[date_col],
                        open=df[open_col],
                        high=df[high_col],
                        low=df[low_col],
                        close=df[close_col],
                    )
                )

                fig.update_layout(title=title, xaxis_title=date_col)
                st.plotly_chart(fig, use_container_width=True)

            elif chart_type == "sankey":
                source_col = config.get("source_column")
                target_col = config.get("target_column")
                value_col = config.get("value_column", config.get("y_axis"))

                if not all([source_col, target_col, value_col]) or not all(
                    [col in df.columns for col in [source_col, target_col, value_col]]
                ):
                    raise ValueError("Required columns not found for sankey diagram")

                # Create unique node labels
                all_nodes = list(set(df[source_col].tolist() + df[target_col].tolist()))
                node_dict = {node: i for i, node in enumerate(all_nodes)}

                fig = go.Figure(
                    data=[
                        go.Sankey(
                            node=dict(
                                pad=15,
                                thickness=20,
                                line=dict(color="black", width=0.5),
                                label=all_nodes,
                            ),
                            link=dict(
                                source=[node_dict[src] for src in df[source_col]],
                                target=[node_dict[tgt] for tgt in df[target_col]],
                                value=df[value_col],
                            ),
                        )
                    ]
                )

                fig.update_layout(title_text=title, font_size=10)
                st.plotly_chart(fig, use_container_width=True)

            else:
                # Default to table or explicit table request
                ChartBuilder._create_table(df, title)

        except Exception as e:
            st.warning(
                f"Chart rendering failed, showing table instead. Error: {str(e)}"
            )
            ChartBuilder._create_table(df, title)

    @staticmethod
    def _create_table(df: pd.DataFrame, title: str):
        """Creates a formatted table display"""
        st.subheader(title)

        # Format numeric columns for better display
        formatted_df = df.copy()
        for col in formatted_df.columns:
            if pd.api.types.is_numeric_dtype(formatted_df[col]):
                if formatted_df[col].dtype in ["float64", "float32"]:
                    formatted_df[col] = formatted_df[col].round(2)

        st.dataframe(formatted_df, use_container_width=True, hide_index=True)